Formatting Query Results to CSV in Oracle SQL Developer

thatjeffsmith SQL Developer 197 Comments

Tell Others About This Story:

You want to run a query or two against an Oracle database.

You want to get the data back into a user-friendly and consumable format, such as a comma separated values file.

You can do this in SQL Developer, VERY easily.

You can watch the movie…

…or read on – your choice!

Quick ResultSet Exports as Script Output

I’m too lazy to hit execute > SaveAs > Open File. I just want to get my delimited text output RIGHT NOW!

The ‘old’ way –

Our old friend, the Export Dialog

And the ‘new’ way (well, new to me!) –

Have the query results pre-formatted in the format of your choice!

The Code

SELECT /*csv*/ * FROM scott.emp;
SELECT /*xml*/ * FROM scott.emp;
SELECT /*html*/ * FROM scott.emp;
SELECT /*delimited*/ * FROM scott.emp;
SELECT /*insert*/ * FROM scott.emp;
SELECT /*loader*/ * FROM scott.emp;
SELECT /*fixed*/ * FROM scott.emp;
SELECT /*text*/ * FROM scott.emp;

You need to execute your statement(s) as a script using F5 or the 2nd execution button on the worksheet toolbar. You’ll notice the hint name matches the available output types on the Export wizard.

NEW FOR 4.1!!!

You don’t HAVE to add the comment to your code anymore. You can JUST use this instead – and ALL of your script output will be formatted by default.

SET SQLFORMAT csv

When you want it back to normal, run

SET SQLFORMAT

Also, in 4.1, we added JSON, so

SELECT /*json*/ * from blah…will come back formatted as JSON.

…OK, back to the story.

Here’s the raw output from the previous examples in case you’re not sitting at your work desk when you read this (click to expand):

SET sqlformat csv
 
SELECT * FROM scott.emp;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-1980 00:00:00,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-1981 00:00:00,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-1981 00:00:00,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-1981 00:00:00,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-1981 00:00:00,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-1981 00:00:00,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-1981 00:00:00,2450,,10
7788,"SCOTT","ANALYST",7566,19-APR-1987 00:00:00,3000,,20
7839,"KING","PRESIDENT",,17-NOV-1981 00:00:00,5000,,10
7844,"TURNER","SALESMAN",7698,08-SEP-1981 00:00:00,1500,0,30
7876,"ADAMS","CLERK",7788,23-MAY-1987 00:00:00,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-1981 00:00:00,950,,30
7902,"FORD","ANALYST",7566,03-DEC-1981 00:00:00,3000,,20
7934,"MILLER","CLERK",7782,23-JAN-1982 00:00:00,1300,,10
 
 14 ROWS selected. 
 
SET sqlformat xml
 
SELECT * FROM scott.emp fetch FIRST 2 ROWS ONLY;<?xml version='1.0'  encoding='UTF8' ?>
<RESULTS>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7369]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[SMITH]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7902]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[17-DEC-1980 00:00:00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[800]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7499]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[ALLEN]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[SALESMAN]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[20-FEB-1981 00:00:00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[1600]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[300]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
	</ROW>
</RESULTS>

So that was kind of a ‘trick’ – I’m not sure it’s a documented feature, although Kris did talk about it WAAAAAAAY back in 2007.

Now you can just Run > Copy > Paste!

Tell Others About This Story:

Comments 197

  1. Hi there, i reead your blog from time to time and i own a similar one and i was just curious iff you gett a loot off spam remarks?

    If so how do you protect against it, any plugin or anything
    you can advise? I get sso much lately it’s driving me mad so any help is
    very much appreciated.

  2. This is looking great for exporting csv, but the sqldeveloper export has a few things that I can’t seem to replicate in sqlcl

    * specifying the export characterset (UTF-8)
    * sending the csv to a file without the blank lines at the end and the message of “25 rows selected.”

    I also don’t seem to be able to turn off the sending of results to the screen. Am I missing something simple?

    1. thatjeffsmith Post
      Author

      >>* sending the csv to a file without the blank lines at the end and the message of “25 rows selected.”
      SET FEEDBACK OFF

      when placed into a script:

      >>I also don’t seem to be able to turn off the sending of results to the screen
      when placed into a script…

      set termout off
      stuff
      set termout on

  3. Hey Jeff,

    Last week at UKOUG Tech you showed how to define your own format, by specifying the delimiters. What’s the syntax for that? Is it already available in 4.1.5 or do we need to use the early adopter relase of 4.2?

    It would be great to have a format for markdown table like this :
    | BEER_ID | BEER_NAME |
    | — | — |
    | 1 | Triple Karmeliet |
    | 2 | Grimbergen |

    Cheers,
    Jerome

    1. thatjeffsmith Post
      Author

      SET sqlformat DELIMITED delimiter left_enclosure right_enclosure

      SET sqlformat DELIMITED | ^ ^
      
      SELECT * FROM hr.locations
      fetch first 5 rows only
      
      ^LOCATION_ID^|^STREET_ADDRESS^|^POSTAL_CODE^|^CITY^|^STATE_PROVINCE^|^COUNTRY_ID^
      1000|^1297 Via Cola di Rie^|^00989^|^Roma^|^^|^IT^
      1100|^93091 Calle della Testa^|^10934^|^Venice^|^^|^IT^
      1200|^2017 Shinjuku-ku^|^1689^|^Tokyo^|^Tokyo Prefecture^|^JP^
      1300|^9450 Kamiya-cho^|^6823^|^Hiroshima^|^^|^JP^
      1400|^2014 Jabberwocky Rd^|^26192^|^Southlake^|^Texas^|^US^
      

      there’s no way to pass a null for the string open and close marks using set sqlformat, although you can set to nothing if you use the GUI/Export

      1. Hi,

        This does not work for me in version 4.1.5.21 of SQL Developer.

        SET sqlformat DELIMITED | ^ ^
        SELECT ‘PLEASE’ COL1,’WORK’ COL2 FROM dual

        “COL1″,”COL2”
        “PLEASE”,”WORK”

        Any idea why the delimiter parameters are ignored? This happens for any select statement that I execute.

        Thanks

      2. thatjeffsmith Post
        Author
    2. thatjeffsmith Post
      Author
  4. Hi Jeff,
    Thanks for your post!
    I was studying all this features, and after making some tests i found that after using Export Tool from grid it seems that the /*csv*/ parameters are overwritten after the exportation.
    But you wrote here when you are using /*csv*/ feature, it is always taking the DEFAULT options for CSV. However, in my case it is taking the LAST parameters after exportation from grid.
    Let me explain this to you with the following steps. I hope you can comment me something related to this:

    1. Write SQL such as:
    select /*csv*/ id, code from mytable; — note: id is INTEGER, code is VARCHAR2

    2. Run it with CTRL + ENTER
    Output: grid with values — ok!

    3. Run the query with F5
    Output: since id is INTEGER and code is VARCHAR2:

    “ID”,”CODE”
    179018,”1057461″
    179097,”32124977″
    179102,”2124977″

    OK!

    3. Get back to Query Result view, and export the grid (manually) with csv option and set Left&Right Enclosure as . Uncheck the Header, as well.
    Output: your CSV FILE as:

    179018,
    179097,
    179102,

    OK!

    4. Run again the query with F5
    Output:
    ,
    179018,
    179097,
    179102,

    The expected output should have been like in the number 3…

    5. So the /*csv*/ keep the last Export Parameters… Am I right?

    It is great for someone who loves the Copy+Paste feature instead of Exporting the grid, because any csv query you run after this steps, will be formatted as the last Export, till you make a new export. And actually for me it’s great since Script Output works faster than Export tool in this kind of job.

    Thanks for your time!

  5. Jeff,

    Very cool. I just tried “set sqlformat json;” and “select /*json*/*from table_1;”, but all records came back on one line. I was expecting to see something similar to an xml output.

    Thanks!

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
      1. No, I should create a xlsx , not xls or csv files, could you tell me is possible create this file and send mail as attached.

      2. thatjeffsmith Post
        Author
  6. Hi Jeff,

    I’m writing a query within a stored procedure utilizing ‘SELECT /*csv*/…’. There are numerous fields selected (some aliased) from numerous tables. I’ve tried, unsuccessfully, to write an output file utilizing ‘utl_file.put_line(OUTPUT_FILE_NAME, output_record) within a FOR LOOP. This stored procedure will be executed by scheduling software and the output file sent to a 3rd party as input for a system we use of theirs. I want to reference a record not all the individual fields in the put_line command. The only time this worked was when I defined a TYPE ……IS RECORD listing all the selected fields, but even then, the output file did not have ticks around the fields or commas separating them. What can I do? Thanks!

    1. thatjeffsmith Post
      Author

      the *csv* stuff is client code – a stored procedure doesn’t know what to do with it

      if you’re writing a SP to generated csv files, you’ll need to code that yourself. but, i’m guessing you can find code samples of this on StackOverflow, the Oracle Forums, AskTom, Oracle-Base, and a dozen other places

      1. Yes, I’ve done that for years. I was asked to investigate if *cvs* could be utilized. Thanks so very much for the info.

  7. Hi Jeff,
    is there any option to set sqlformat to PDF while using spool utility?
    My motto to export resultset into pdf file.Need solution asap.

    1. thatjeffsmith Post
      Author
  8. Hi Jeff,

    Wanted to know how we can print text in a csv file via oracle starting from any specific cell ? Like for ex I want to print a ‘Disclaimer’ at my csv generated file from the ‘I’ column and not from the ‘A’ column of the csv.

    Thanks for the help in advance

  9. Hi Jeff,

    I like this feature and have found it useful, but I’ve encountered an oddity when formatting to csv.

    In my query, for one output column I specify within my select, “NULL AS “fieldName2”. When I create a csv, “fieldName2″ comes out with a value ,””, as opposed to ,, for a column in the select which has null values from the source.

    I’m probably overlooking something obvious, but have searched and haven’t found what I’m missing.

    1. I would suspect that fieldName2 was a varchar and the null is represented by an empty string. If a number you would expect ,, (note that there are surrounding commas)

      Steve

  10. I have one shell script.script is used to connect database and run sql quey and result should be send to excel file.My scrpt is working good but after excueting my script the excell file showing results in unformated way. please help me how to solve the issue.

    1. thatjeffsmith Post
      Author
  11. Hi Jeff. I’m using SQL Developer and wanting fixed column output from my query result. Example: 1st field, position 1 and length 1, 2nd field begins position 2 and with length 6, and so on. And to have results in a .txt file format with output fields in particular column positions. Thanks!

  12. I am running with Version 3.2.20.09. I don’t your results when using csv nor insert. Is there something I can use to get these or similar results? Please advise.

    Thanks Jeff…

    1. thatjeffsmith Post
      Author
  13. Please also add these export hints to the Ctrl+Enter thing, not just F5. I use the Ctrl+Enter all the time to run single statements.

    1. thatjeffsmith Post
      Author
      1. Would it be an alternative to not use the grid and instead do the text output if there is output hints present? I.e. if you write select /*insert*/ … etc it’s clear that you don’t want the result in a grid.

        I love the Ctrl+Enter feature and use it all the time for ad hoc queries and while creating scripts. It would be very convenient when one creating scripts to be able to quickly generate e.g. an insert statement with the correct column names etc without doing the F5 thing in a new empty worksheet.

      2. thatjeffsmith Post
        Author
      3. It may need some plumbing, but I’m sure more people than me would be happy to use it.

        I know I have missed it several times. I have just written the hint, pressed Ctrl+Enter, and gotten surprised when it didn’t work. It’s not at all obvious for a normal user that they are fundamentally different under the hood. Especially if you’re switching back and fourth between SQL Developer and SQL Server Management Studio. Running one statement or a script isn’t a big difference in SSMS.

      4. thatjeffsmith Post
        Author

        yeah, cause there’s no SQL*Plus in SQL Server world, but I get your point

        if we would do that, it would mean you’d have to take the comment out to have your data come back in a grid. so if you wanted one version of your query, and to be able to get a grid to read or the formatted script output out, you’d lose that flexibility

  14. Hello,

    I’m having an issue with the text format option, I get a ‘null’ string instead of a tab character as field separator. It’s even on the original post example:

    “EMPNO”null”ENAME”null”JOB”null”MGR”null”HIREDATE”null”SAL”null”COMM”null”DEPTNO”
    7369null”SMITH”null”CLERK”null7902null17-DEC-80 12.00.00null800nullnull20…

    Is there any way around this to get an actual tab character in the output? This should be the default behavior for the text format. I know tha csv would be a better option, but It’s not the case for my locale as we use a comma for the radix point instead of a point, so the numeric fields get messed up.

    thanks.

    1. thatjeffsmith Post
      Author
    2. I bumped into the same. What worked for me was to do the export the manual way as explained. Then I did the same again the script way with /*text*/, the ôutput then was correct, tab and not null between the values.

    1. Now I got this error message.
      Error at Command Line:9 Column:36
      Error report:
      SQL Error: ORA-00904: “REC_IE”: invalid identifier
      00904. 00000 – “%s: invalid identifier”
      *Cause:
      *Action:
      But I didn’t specify any field names like “REC_IE”
      select /*csv*/ AU_JOB_ID,AU_TSK_ID,REC_IE,SRC_SYSTEM, CORE_SRC_SYS_ID,AU_SITE_NM,CORE_TRD_ID,
      BIZ_DT,
      BOOK_ID from

  15. When I try to run query as script “select /*csv*/ * from ‘ I couldnt see results and query status shows completed in xx seconds. Query will return 1000000 records. I set the limit in preferences-workbook as well. Pls help

    1. thatjeffsmith Post
      Author
      1. Its not working for any queries. Before I change max limit in preferences it worked, but after the limit set to 100000 its not working. I tried to execute the same worked query after reset the limit to 5000 not working. Just getting the message task completed in 0 seconds if I run query as script. But for the same query getting results if I run query using run statement.

      2. thatjeffsmith Post
        Author
      3. Thank you for the update. I tried twice restarting. File-Exit and reopened sql developer. Still facing same problem.

      4. thatjeffsmith Post
        Author

        run sqldeveloper in debug mode – in the sqldeveloper.conf file in the bin directory, change the last line to read ‘debug.conf’ vs ‘non-debug.conf’, start up sqldev, try the script execution, and copy the contents of the logging panel here

      5. after restart first run as script produces result and if I trigger the same query again its not running. Getting the message task completed in 0 seconds if I run query as script. But for the same query getting results if I run query using run statement.

      6. thatjeffsmith Post
        Author
  16. Hello Jeff,

    First of all, thank you for all the tutorials you have posted on your blog. I visit your blog frequently when I am looking for solutions in SQL Developer. I tried Select /*loader*/ for one of my script that returns about half a million records, however it always throws Java error (read dead end). The same script worked fine with just over a thousand records.

    Do you know what can be the reason that I am getting this error? I am running SQL Developer on citrix received as it is hosted on the vendor site.

    Thank you,

    1. thatjeffsmith Post
      Author

      2 things stand out in your post ‘half a million records’ and ‘Citrix’…I’m guessing there’s not enough resources allocated to your Citrix workspace for SQL Developer to do what you asked it to do.

      In your script, spool that output to a file, and minimize the output panel in SQL Dev – that might help. If not, ask your Citrix admin for more RAM

      1. Thank you JEff, I wll check with them.

        Meanwhile, just wanted to share the error message I am getting.

        java.io.IOException: Read end dead
        at java.io.PipedInputStream.checkStateForReceive(PipedInputStream.java:246)
        at java.io.PipedInputStream.awaitSpace(PipedInputStream.java:252)
        at java.io.PipedInputStream.receive(PipedInputStream.java:215)
        at java.io.PipedOutputStream.write(PipedOutputStream.java:132)
        at java.io.BufferedOutputStream.write(BufferedOutputStream.java:105)
        at sun.nio.cs.StreamEncoder.writeBytes(StreamEncoder.java:202)
        at sun.nio.cs.StreamEncoder.implWrite(StreamEncoder.java:263)
        at sun.nio.cs.StreamEncoder.write(StreamEncoder.java:106)
        at sun.nio.cs.StreamEncoder.write(StreamEncoder.java:116)
        at java.io.OutputStreamWriter.write(OutputStreamWriter.java:203)
        at java.io.Writer.write(Writer.java:140)
        at oracle.dbtools.raptor.format.ResultsFormatter.write(ResultsFormatter.java:229)
        at oracle.dbtools.raptor.format.CSVFormatter.printColumn(CSVFormatter.java:51)
        at oracle.dbtools.raptor.format.ResultSetFormatterWrapper.print(ResultSetFormatterWrapper.java:95)
        at oracle.dbtools.raptor.format.ResultsFormatter.print(ResultsFormatter.java:176)
        at oracle.dbtools.raptor.scriptrunner.SQL.executeQuery(SQL.java:242)
        at oracle.dbtools.raptor.scriptrunner.SQL.run(SQL.java:48)
        at oracle.dbtools.raptor.scriptrunner.ScriptRunner.run(ScriptRunner.java:170)
        at oracle.dbtools.raptor.scriptrunner.ScriptRunner.run(ScriptRunner.java:292)

        Thank you again!

  17. If you can make it through the spam, here a quandary…

    How can you set your /*insert*/ up to use a DIFFERENT table name?

    I’m trying to pull some records for a demo, and wanted to use a selected table name. I tried aliasing, but the /*insert*/ ignores my druthers.

    I can’t do
    select /*insert*/ * from scott.emp demo_records ;

    Aside from doing something crazy like passing the table through a view, are there any ideas?

    Ultimately, I can overcome this in a few minutes w/ NotePad++, but it would be better to not have to step out of the tool.

    Marc

    1. thatjeffsmith Post
      Author

      Use the wizard, write the query as normal, execute with ctrl+enter. On the results grid, right-click, export. Choose INSERT – input your table name.

      Also, our editor supports search and replace, so no need for NotePad++, at least not for this task.

      1. Ah, that’s my fault for trying to use the functionality in this post. Sometimes, it’s best just to do it “the old fashioned way”. 😉

        > our editor supports search and replace, so no need
        > for NotePad++, at least not for this task.

        I’ve used the SQL-D editor, but I’m mangled a lot more than I’ve fixed, so I stick to doing it “the old fashioned way”. I’m a wiz at NP++, and it never accidently deleted my queries or substituted items from the query history… both of those still catch me when I’m trying to speed along and hit the wrong CTRL- combination. I know I can turn individual keyboard shortcuts off, but it’s easier just to jump to NP++ for editing, and use SQL-D mainly for initial coding and executing.

        Thanks for the super fast response, Jeff!

        Marc

      2. thatjeffsmith Post
        Author

        >>and it never accidentally deleted my queries or substituted items from the query history
        Undo will bring back your query if a SQL History item is brought in an clobbers your text

        These formatter comments will work great if you need their default behavior. If you need to tweak them, such as supplying a different table name, then it’s back to the GUI for exporting grid contents.

        We did have a bug for inserts on some trickier selects where we we’re getting confused on the table name, that’s been fixed for our 4.1 patch we have in the works.

    2. Aaaand, because I always hit SEND to soon…

      I also noticed that the behaviour was different, depending upon the case of the hint.

      select /*insert*/ * from my_Demo_taBle_nAME ;
      — hastily and poorly typed

      My resulting insert statements were “” the table name – a dangerous practice if not used going IN to the subject query. I was getting:

      Insert into “my_Demo_taBle_nAME” (…

      instead of

      Insert into my_Demo_taBle_nAME (…

      So, looking back over previous comments, I tried capitalizing the hint:

      select /*INSERT*/ * from my_Demo_taBle_nAME ;

      and the result is a space-aligned table in the script output.

      RECNO UNIQ SYM MCHTY
      ———- ———————————— —— —–
      407231 000024898 198401 2
      407232 000024898 198407 6

      THAT’S not what the doctor ordered!

      Marc

      1. thatjeffsmith Post
        Author

        we’re java, so the comments are case sensitive, the formatter type has to match the label EXACTLY as shown in the GUI dialog in the grid context menu…so /*insert*/ and not /*INSERT*/

      2. I’m also getting insert statements with a pair of double-quotes instead of a table name. Is there a SET statement that I can add to fix that?

        SQL Developer v 3.2.20.10.20

      3. thatjeffsmith Post
        Author
      4. I’ve simplified the fields since I’m only pulling some of them:
        SELECT
        /*insert*/
        *
        FROM
        MATRIX.OCCUPATIONS
        WHERE
        VERSIONID =
        &versionid;

        I get output that looks right except for the table name. Here’s some truncated output:
        REM INSERTING into
        SET DEFINE OFF;
        Insert into “” (OCC_CODE,OCC_NAME, ….

      5. Hi Smith,

        I am die hard fan of SQL Developer, and really helps me a lot in my day to day life, somehow, I have to work with Java, and write the data of all tables in csv file, so could you please point me to the jar file of oracle sql developer, and the method which does exactly same functionality as the gui tool does. Hoping for your favorable reply.

      6. thatjeffsmith Post
        Author

Leave a Reply

Your email address will not be published. Required fields are marked *