1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Question on exporting in sql developer

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Jan 25, 2017.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Hi,
    I know the usual way of right clicking on the object and exporting.

    Is there a way of writing the sql query or script to get the objects exported?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes.

    I suppose that's not the answer you were looking to receive but it does answer the question you asked. A better question would be:

    How do I write the query to export table data in a [CSV,fixed-width,text,etc.] format?

    where you choose which format you want. That question requires more than a simple Yes or No and is likely what you meant with the question you did post.

    Clarify your question, please.
     
  3. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Hi David,
    I am sorry for not posting correctly.
    Yes I am looking for the query to export table data in a [CSV,fixed-width,text,etc.] format?

    Kindly help me with the query?


    Thank you,
    Shanmuga Priya D
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What format do you want? The query depends on how you want the records constructed.
     
  5. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Hi David,
    I need for csv type.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That one is easy; the hardest part is writing a script to generate another script that will create your file so all you need to do is pass in the table name to get a script specific for that table. Let's use the ALL_TAB_COLUMNS view to get this done:

    Code (SQL):
    SET linesize 2000 trimspool ON

    spool &&1._data_dump..SQL

    DECLARE

        cursor get_tab_cols (ptabname IN varchar2) IS
        SELECT column_name
        FROM all_tab_columns
        WHERE TABLE_NAME = UPPER('ptabname');

        vcolct    NUMBER;
    BEGIN

        EXECUTE immediate 'select count(*) from all_tab_columns where table_name = '''||ptabname||'''') INTO vcolct;  
        dbms_output.put('select ')l
        FOR tcols IN get_tab_cols loop
            IF vcolct > 1 THEN
                dbms_output.put(tcols.column_name||'||'', ''||');
                vcolct := vcolct - 1;
            ELSE
                dbms_output.put_line(tcols.column_name||' from '||ptabname||';');
            END IF;
        END loop;

    END;
    /

    spool off

    spool &&1._csv..txt
    @&&1._data_dump
    spool off

    SET linesize 80 trimspool off
     
    Using PL/SQL gets all of the column names on one line with the comma generation included. Save this to a file so it can be run from the SQL> prompt in this manner:

    SQL> @[name_of_your_script] table_name

    If you name this csv_generator.sql and run it against the EMP table you would run it this way:

    SQL> @csv_generator emp

    The case of the table name will be taken care of by the script. (Since I don't have a database to test this on be prepared to make adjustments to get the script to generate correctly.)
     
    Sadik likes this.
  7. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Thank you very much David. I'll test this from my end. Thanks again :)