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.


When you want it back to normal, run


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;
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' ?>
		<COLUMN NAME="HIREDATE"><![CDATA[17-DEC-1980 00:00:00]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[20-FEB-1981 00:00:00]]></COLUMN>

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

      >>* sending the csv to a file without the blank lines at the end and the message of โ€œ25 rows selected.โ€

      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
      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 :
    | — | — |
    | 1 | Triple Karmeliet |
    | 2 | Grimbergen |


    1. thatjeffsmith Post

      SET sqlformat DELIMITED delimiter left_enclosure right_enclosure

      SET sqlformat DELIMITED | ^ ^
      SELECT * FROM hr.locations
      fetch first 5 rows only
      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 of SQL Developer.

        SET sqlformat DELIMITED | ^ ^


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


        1. thatjeffsmith Post
    2. thatjeffsmith Post
  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:



    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:



    4. Run again the query with F5

    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.


    1. thatjeffsmith Post
    1. thatjeffsmith Post
        1. thatjeffsmith Post
  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

      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
  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)


  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
  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 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
  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
      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.

        1. thatjeffsmith Post
          1. 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.

          2. thatjeffsmith Post

            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:

    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.


    1. thatjeffsmith Post
    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”
      But I didn’t specify any field names like “REC_IE”
      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
      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.

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

          2. thatjeffsmith Post

            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

          3. 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.

          4. thatjeffsmith Post
  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

      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. Read end dead
        at sun.nio.cs.StreamEncoder.writeBytes(
        at sun.nio.cs.StreamEncoder.implWrite(
        at sun.nio.cs.StreamEncoder.write(
        at sun.nio.cs.StreamEncoder.write(
        at oracle.dbtools.raptor.format.ResultsFormatter.write(
        at oracle.dbtools.raptor.format.CSVFormatter.printColumn(
        at oracle.dbtools.raptor.format.ResultSetFormatterWrapper.print(
        at oracle.dbtools.raptor.format.ResultsFormatter.print(
        at oracle.dbtools.raptor.scriptrunner.SQL.executeQuery(

        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.


    1. thatjeffsmith Post

      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!


        1. thatjeffsmith Post

          >>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.

      ———- ———————————— —— —–
      407231 000024898 198401 2
      407232 000024898 198407 6

      THAT’S not what the doctor ordered!


      1. thatjeffsmith Post

        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*/

        1. 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

          1. thatjeffsmith Post
          2. I’ve simplified the fields since I’m only pulling some of them:
            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, ….

        2. 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.

          1. thatjeffsmith Post
  18. It’s genuinely very complex in this active life to listen news on TV, thus I just use world
    wide web for that reason, and take the most up-to-date information.

  19. you are in point of fact a excellent webmaster. The web site loading speed is amazing.
    It sort of feels that you are doing any distinctive trick.
    Furthermore, The contents are masterpiece. you’ve done a fantastic activity in this subject!

  20. 25) Grease – $506+ million ($188+ million in 1978).
    Whilst easy to operate it had limited ability to import common format files but its
    biggest draw back was the ME operating system itself. As media shifted from newspapers to a more online focus, pictures
    and video began to appear of movie stars walking down the street, shopping, or
    eating in a restaurant.

  21. I would like to use the SELECT /*csv*/ * from yada yada yada; with a DISTINCT command. Is that supported? Running

  22. I tried SELECT /*csv*/ * FROM PLM.MxLattice;

    But I still just got the plain old column results…
    Using SQL Developer

    1. thatjeffsmith Post
  23. It’s a pity you don’t have a donate button! I’d most certainly donate to this superb blog!
    I guess for now i’ll settle for book-marking
    and adding your RSS feed to my Google account. I
    look forward to brand new updates and will talk about this site with
    my Facebook group. Chat soon!

  24. Hi Jeff,
    I just started using Oracle after many years using MS SQL Server. I’m glad I found your site and will be back many times, I’m sure.

    I have a question about this tip…
    When I tried using the /*text*/ version with my query, it removed the first column name from the header row. My query was not “select /*text*/ * from table”. I specified which fields were needed in a statement using joins. Have you run across this before?

    Kind regards,

    1. thatjeffsmith Post
        1. thatjeffsmith Post
          1. thatjeffsmith Post
  25. Patricia and Levi Haynes and Rakesh reported /*CSV*/ or spooled output would sometimes fail after one successful try. I am having the same problem, zero output, after 5 or 3 or sometimes just 1 success(es).

    My lousy work-around has been to kill the program with Task Manager, just out of paranoia that some setting will be saved and it will never work again. Levi reported that just exiting normally and restarting is all that is needed?

    Is there a setting for this feature? Spool size? Purge buffer? I have perused the settings. I only have 2 browse instances, Excel (68kb file) and Oracle SQL developer running.

    Thanks for your service, I hope management appreciates what a good face you provide the Oracle name! The greatest tool is no better than the staff that support it.

  26. Hi,

    Your blog is wonderful, a lot of great articles and tips !

    But, I can’t do this tip on my computer … :'(
    – win 7 – 64bit
    – Oracle Dev (MAIN 17.29)
    – Java 8.0_31

    The output isn’t formatted .. Any idea ?

    Thx a lot

    1. thatjeffsmith Post
  27. Hi Jeff,
    Great article, but I have problems to reproduce the results with the latest version.
    Do those hints (e.g. select /*html*/ ) still work in SQL-Developer
    I could reproduce all of your examples with version but not with

    1. thatjeffsmith Post
      1. [Solved]
        I’ve got it running and it’s even easier to handle via sqlformat instead of inline hint.

        set sqlformat html;
        select * from dual;

        1. thatjeffsmith Post
        1. thatjeffsmith Post
  28. Hi Jeff,

    I ran this script:

    spool datefromdual.csv
    select /*delimited*/ sysdate from dual;
    spool off;

    and got this output in the csv file:

    > select /*delimited*/ sysdate from dual
    06-JAN-15 11.18.16

    But I don’t want the SQL statement to be spooled in the csv file. I just want the column header and data, any idea how to ignore the SQL statement?

    1. thatjeffsmith Post
  29. I’ll check out the link – thanks!

    I have tried linesize 30000 and I got: warning: linesize set to 2000 maximum (32767 specified)

  30. Came across this and I’m thrilled! This seems to be exactly what I was looking to do. However, when I tried it out, the results aren’t exactly what I anticipated. I’ve set my delimiter to ‘~’ because I know I won’t have any text fields with that character. When I SPOOL to a CSV, I get the SQL statement in there (I don’t want that) and the column names are repeated for each record returned with ‘————–‘ under the column name. Also, the delimiter doesn’t seem to be showing up between each field. Any idea what might be going on and how to get this to work the way I need it to?

    1. thatjeffsmith Post

      Don’t use the /*delimited*/ or /*csv*/ hint – you have more control using the Export dialog. In the export window, set your format to delimited, your delimiter to ~ and uncheck ‘header’. Then export to whatever file you desire.

      If you use the SPOOL command in a worksheet, you’ll be left with the default formatter options when it formats the output to say CSV. You can avoid getting the statement in there if you use the SET ECHO OFF command in your script.

      1. Thanks for your reply, Jeff! While the dialog is helpful, this is a script that I need to automate to pull a nightly extract of a table in CSV format. I wanted to use the hint because selecting each column and concatenating it with a delimiter would get quite cumbersome. It is a very wide table and the columns are frequently changing, for this reason, I was hoping to be able to do a select * with a delimiter.

        SET ECHO OFF removed the SQL statement from the export, so that was a helpful step in the right direction!

        1. thatjeffsmith Post

          Yeah, I hear ya. A few folks have build pl/sql solutions to exactly what you want and have published them on their blogs. You could check those out and see if they get you even closer.

          1. Might you be able to point me in the direction of these blogs? I have yet to find a solution that will work for me, given that my line-size will be in excess of 2000 (which seems to be the max size for the linesize parameter)

          2. thatjeffsmith Post
  31. Hello,
    When I did an export to csv o loader I have double quotes in the results.
    Where can I change that? I don’t want de double quotes in the results.
    I have Oracle SQL Developer 1.5.5 Versiรณn 1.5.5.
    Thanks a lot!

    1. thatjeffsmith Post

      In the export dialog, set the Left and Right Enclosure characters to null – they default to ” and ”

      I’m not sure that’s even an option in v1.5 – that’s like 7 or 8 years old. Can you upgrade to something from this decade?

        1. thatjeffsmith Post

          If you need help convincing them, let me know.

          You can also get the latest version from OTN – there’s no installer, so no admin rights required, to get it up and running.

  32. Love the idea.. just not as useful as i would like.. Is there no way to script a call to the export utility.. All we would need to do is call the export utility and pass it a name for the file. and the rest could use defaults you have configured in the preferences. I would guess this is impossible but sure would be nice…

    1. thatjeffsmith Post

      You can for tables – you can use the command line interface to export the contents of a ‘cart’ to one or more CSV files. But that’s about it for today. Otherwise you could do this with SQL and an OS job to run it via SQL*Plus.

  33. hi Jeff,
    your post is very helpful. I have a doubt and hope u can clarify it

    I would like to issue a select query on my data base and use input for my where clause from a text file. It will be something like:
    select * from employee where employee_id in ‘C:\sample.txt’

    can u please correct me, as in where i am going wrong and what should be the format of my input file.

    we are currently using SQL Developer 3.1.07.

    Many thanks in advance

    1. thatjeffsmith Post

      yeah, you can’t do that – in the SELECT you’d have to reference the actual values, OR load the text file as an EXTERNAL TABLE and use select * table where col1 not in (select col2 from external_table)

  34. Hi Smith,

    I was surprised to see a query which will export the data in intended format, but when i used it i was not getting any output

    I’m using SQl developer 4.0, as u said above im not able to see any output, once i run query and hit F5 its running and then there is no output.

    Do i need to change any sttings to export data to specific format(csv,text etc)


    1. thatjeffsmith Post

      What version of 4 are you running?

      What version of the JDK are you running?

      If you try it with HR.EMPLOYEES or say DBA_DATA_FILES – does it work?

      Can you try on your query with a smaller resultset?

      I have also seen where an application restart can help with this behavior.

      1. Sql developer Version, Well im not sure of JDK ,where do i can check this.

        I tried with a table having 4 rows(each row has 4 columns), still i couldnt get output, but as i already told query getting executed, getting message like Task accomplished in some seconds but no progress further.


        1. thatjeffsmith Post
          1. hi jeff,

            I was facing the same issue reported by rakesh but not anymore. Once i restarted the application the query is working perfectly fine. you were right about this issue.

            Thank you

  35. The hints are not working in version:
    In version 3.X it works only 1 time ๐Ÿ™

    I currently setup: Preferences – Database – Utilities – Export.
    I make “Save as” to “clipboard” for a fast export.

    Even faster would be an improvement option in the right click menu, below “Export…”
    – Predefined export

    1. thatjeffsmith Post
  36. Thanks Jeff. This seems to be working fine now. Last week, the first time I ran the script, it would work – but then anytime after that it would just produce blank files. I’d have to close SQL Sev and reopen it. Anyway…

    I’m using SQL Developer version When called as a script, the below does exactly what I need it to. Except… the csv files created have 2 blank lines at the end. I’m passing these files through a DataStage job, which expects 1 blank line (technically, a line feed at the end of the last record) – but not 2. Any ideas on how I can modify this to have 1 blank line instead of 2? I know, completely random question…

    SPOOL “C:\MY_TEST_FILE1.csv”;
    select /*delimited*/ yada yada yada;

    SPOOL “C:\MY_TEST_FILE2.csv”;
    select /*delimited*/ yada yada yada;

    SPOOL “C:\MY_TEST_FILE3.csv”;
    select /*delimited*/ yada yada yada;

    1. thatjeffsmith Post

      Hmmm, does SQL*Plus do the same thing – 2 sets of cr/lf pairs at the end of the file?

      There’s a newer version of 3.2 – 3.2.2, and there’s version 4.0.1 – but I don’t think there have been any changes here.

      1. Hi Jeff. Me again. I’m currently writing a VB script that will loop through the csv files generated by the script I mentioned above, and will remove the final cr/lf (the script creates 2 sets of cr/lf pairs at the end of the file when run from SQL Dev), as well as removing all double quotes. This seems a bit excessive. With the exception of the double quotes and the 2 sets of cr/lf pairs at the end of the file – and seemingly no way to avoid these – the /*csv*/ hint works perfectly for me. But if I then have to run another script to remove the extra line feed and double quotes…
        So… in your opinion… what is the best method to run ~160 queries, with each query saving the results as individual csv files (I just need comma delimited, this has nothing to do with Excel)? I’ve seen an option for basically reconfiguring my SELECT statement by concatenating all of the fields together with the appropriate commas and formatting as needed, but again, with 160 queries (and growing), that would be a monumental task.
        I may be grasping at straws… but there must be a solution for this? Really appreciate your help – and quick responses.

        1. thatjeffsmith Post

          I would write a stored procedure…or a perl script. Whichever you know better. Perl is better at handling strings and it has a DBD package for Oracle that makes running queries pretty easy.

          Then handle the data and files exactly as you want to.

          1. thatjeffsmith Post

            Perl also has a qq() function that will handle quoting the strings for you whenever necessary – so if you’re text is Daddy’s Boy, it will go in as ‘Daddy”s Boy’ … a real life saver.

          2. Appreciate the quick response Jeff! Unfortunately, I know neither. I’ll work with my ETL guys to see if they can come up with something. Thanks!

  37. The /*csv*/ works beautifully when combined with SPOOL to export my results automatically to a csv file. However, it only works the first time – subsequent runs of the script produce nothing, just empty files. If I close and reopen SQL Developer, then it will work again – but only the first time. Any suggestions? My script runs 160 queries and ideally would output the results of each query as individual csv files. While the /*csv*/ is doing exactly what I need it to do, closing and reopening SQL Developer 160 times will defeat the purpose. Good stuff here. Thanks!

    1. thatjeffsmith Post

      This is working for me in the current released versions – I’m not sure what version you are running or what your script looks like?

      spool c:\test0.csv

      select * /*csv*/ from scott.emp;

      spool off

      spool c:\test1.csv

      select * /*csv*/ from scott.dept;

      spool off

      SPOOL C:\test2.csv

      select * /*csv*/ from HR.locations;

  38. Yes, you are understanding the issue correctly. However, I am not getting the same results. Even if I were, I wouldn’t have expected that output either. First, I wouldn’t expect text qualifiers on a fixed-width record since each field value is expected to be in a certain set of positions on the record. Therefore, the qualifiers are not necessary. Second, I wouldn’t expect the output to be separated like they are in the columns. While the data should be in columns (as dictated by each field’s positions in the record) I don’t suspect Salary is such as large field that it would cause Commission_Pct to start so far to the right of Salary. Seems SQL*Developer is outputting the value in columns that are sized based on some factor other than the size of each field.

    1. thatjeffsmith Post
  39. Hi,

    I am trying to export data from SQL*Developer in a fixed-width format. When using the “Fixed” export format, the data is exported with double quote qualifiers and is not aligned in fixed-width columns.

    For example, assume I am exporting five fields of lengths 6, 7, 30, 4, and 2. I would expect the data to look like the following with regard to fixed-width formatting:

    100000***0030 0047RG
    100001***0002JAVA PORT Account 1111 Test 0001EQ

    Note that in the above example, the first record does not have a value for the third field.

    Instead of getting an output similar to the above format, SQL*Developer is creating the output as follows:

    “100000” “***0030” “” “0047” “RG”
    “100001” “***0002” “JAVA PORT Account 1111 Test””0001” “EQ”

    First, it is putting the data in double-quote text qualifiers. And as you can see, the data is not aligned properly. Since the first record does not have a value for the third field, the fourth field is starting in a position lower than the fourth field on the second record.

    Can SQL*Developer output tables in true fixed-width format and if so, how can that be done?


    1. Update:
      I noticed that in my post the example didn’t keep spaces in the first record. So I am including the example here again and replacing spaces with “~”.

      Please note that due to the font style, the characters in the above example are not aligning in columns as they would with a Courier font. In the above example, 0047RG should be left aligned with 0001EQ.


      1. thatjeffsmith Post

        If i run

        FIRST_NAME ,
        LAST_NAME ,
        SALARY ,

        I don’t see any issues from the NULLs in the commission_pct column messing up the alignment of the department_id field…am I understanding your issue correctly?

        Here’s how it looks for me:
        fixed field export

  40. I am using the latest version ( build MAIN-13.80) on a Win7 64bit machine, with jdk1.7.0_45.

    I am trying to spool output to a csv file using SELECT /*csv*/ * FROM table.

    The table has 230 records. Only the first 50 lines appear, then there is an Error in the script output. When I click on the ScriptRunner Task (failed) I see “Java heap space” then more details:

    java.lang.OutOfMemoryError: Java heap space
    at java.lang.reflect.Array.newArray(Native Method)
    at java.lang.reflect.Array.newInstance(

    I’ve tried changing Preferences…Database…Advanced…SQL Array Fetch size from 50 to 200 (I get 200 lines out).
    I’ve tried changing JVM settings in product.conf to increase memory from 880M to 1250M. No change.

    Can you suggest anything to try?

    1. thatjeffsmith Post

      I’ve noticed you posted the same question on the forums – I’d suggest we continue the conversation there, but…

      1. how wide is this table?
      2. does this work for other tables or queries?
      3. 200 is too high – that will require MORE memory, take it back down to 50 or 100
      1. Hi Jeff,

        Yes, I only kept array size at 200 to test. Back down to 50.

        Yes, this is a wide table. 200 columns.

        It does work for other tables and queries. I’d thought it also worked for a different table that had a similar large number of columns- but I cannot reproduce that now.

        1. thatjeffsmith Post
  41. Jeff, is this support extensible at all? Could someone write their own formatter that output something different such as c# or java objects?

    1. thatjeffsmith Post
    1. thatjeffsmith Post

      Yes, we actually had it in the EA for a few months, but a bug pushed it out for the final. I think it would help if you could provide a use case or 3 to help justify adding it back ๐Ÿ™‚

      1. For me I have a large web project I’m working on, it would be an awesome way to be able to mock api results so the front end isn’t held up by building the rest layer.

        1. thatjeffsmith Post
          1. I do, haven’t had a good chance to dig in yet. Sometimes you just need some quick sample data. Personally I’d rather keep my API separate from the database.

  42. In the latest version ( build MAIN-13.30) special format of
    SELECT /*CSV*/* FROM DUAL is not working. Is this has been disabled in latest version of SQL Developer? Please help

    1. thatjeffsmith Post
  43. Hi. Great info. Thanks. I have two questions:

    1. I am spooling to a .csv on my local hard drive and it works wonderfully except for one thing–the command is included in the output. For me to use the output as a table in the future (which I plan to do) I would need to remove the first line for each file–a pain. I have tried the SET command for FEEDBACK, ECHO and VERIFY but no luck. Any suggestions?

    2. Running as a script works great–once! For it to work again I need to shut down SQL dev and re-invoke. Is there a way around this?

    Thanks for any assistance

    1. thatjeffsmith Post

      1. That’s the same behavior you’ll see in SQL*Plus, regardless of set echo setting, the statement will be included in the SPOOL file – and our goal is to emulate SQL*Plus, even if it doesn’t necessarily make sense.
      2. What version of SQL Developer are you using? And is it just this particular script, or any script that refuses to run more than once?


    1. thatjeffsmith Post

      Does it work with the other formats like XML or HTML? I’ve seen this ‘quit working’ for me as well from time to time, but an application restart always fixes that.

      I’ve also seen it quit working on a very specific table – so if it’s not working for your query, try it against SCOTT.EMP or similar to see if it’s also broken there.

      1. Hmmm….yes, they all work except for /*csv*/ and /*text*/.
        /*delimited*/ works so I can use that instead of the csv. Thanks for your help.

  44. The /*csv*/ trick does not seem to work with queries that start with the WITH clause.
    Is there a way to achieve the same effect for long running queries (10 mins) without having to do a 2 step operations which seems to re-run the query to export the results.

    1. thatjeffsmith Post

      Actually that works for me. Just put the hint in the SELECT, not the WITH

      WITH q AS
      (SELECT owner,
      sysdate – NVL(last_analyzed,sysdate-1000) last_analyzed
      FROM all_tables
      WHERE owner IN (USER, ‘HR’, ‘SCOTT’, ‘SH’)
      SELECT /*csv*/ ‘SQLDEV:GAUGE:0:’
      || MAX(last_analyzed) over ()
      || last_analyzed last_stats_chart,
      ROUND(last_analyzed) days_since_last_stats
      FROM q;

  45. Thank you for this post.
    I use html output most often for Oracle Apps. The output looks great for multiple queries’ output saved to one single html file.
    I notice a Search box for each of the query output though. Any idea?

    1. thatjeffsmith Post
  46. Hi Jeff,

    Thank you for this post, it’s great.
    My script works great when I run it in SQL Developer and outputs the results as desired (csv format etc).
    I have one problem, however, which has been bothering me. I have been trying to set up a scheduled task in windows to run a bat file which has the command (sqlplus -S username/[email protected] @C:\mydirectory\myscript.sql).
    The problem is that the output is not in ‘csv’ format in that what should be on one row is being put in multiple rows (some columns on one row) without commas and between each record I am getting line breaks.
    Can you/anyone else help with this?


    1. thatjeffsmith Post
      1. Sorry, not sure I understand what you mean.
        I should have mentioned, this is my script- which works find in sql developer:

        set feedback off
        set pagesize 0
        set termout off

        column dt new_val dateAdded
        select to_char(sysdate,’yyyymmdd’) dt from dual;

        spool C:\mydirectory\myfile_&dateAdded..csv

        SELECT /*csv*/ field1
        (SELECT ‘field1’ …FROM…)
        order by field2, field3;
        Spool off

      2. OK, I think I understand…so /*csv*/ does not work with SQLPlus?
        What would be the way to get the output from sqlplus?

        Or if there is a way I can schedule my script to run from SQL Developer it would be fine too. I need to produce a daily file which I’m having to run manually at the moment.


        1. thatjeffsmith Post


          So you would need to write your script using SQL*Plus formatting commands to get the same desired effect. I’m pretty sure Tom Kyte has a solution posted on his AskTom site.

          We have a command-line interface for SQL Developer, but it doesn’t have a plug for the worksheet and issuing scripts…YET.

  47. Hi Jeff, I’m using this code to import data into folder using sql developer
    spool c:\spool_text_hint.csv
    select /*csv*/ * from cdm where subject like %abc%xyz;
    but every time i need to change the subject like there any macro we can use in sqlo developer…


    1. thatjeffsmith Post
      1. Thank you very much for reply. I have seen it, but what i am trying to do in below code is
        spool c:\spool_ABC.csv
        select /*csv*/ * from cdm where subject like ‘%ABC’;
        i have mentioned ABC in spool and select statements, which every time i need to change.Instead of 2 changes if i made 1 change which will automatically apply to the second one.

      2. Hi Jeff, my query has been resolved, i have used ‘&’ to change the code.
        I’m having one more query is it possible to create a BAT file to execute all my .sql scripts using sql developer?

        1. thatjeffsmith Post
    1. thatjeffsmith Post
  48. Hi Jeff, when I use the /*insert*/ in my SELECT statement, my table name is returned in double quotes like this:

    Insert into “table” (id, name) values (100, ‘Bill’);

    This requires me to perform an extra step to remove the quotes as Developer throws an error if I try to run the statement as is. Any suggestions?

    1. thatjeffsmith Post

      I just tried this and got something different…

      select /*insert*/ * from scott.emp;

      REM INSERTING into scott.emp
      Insert into scott.emp (EMPNO, …

      You could try changing your table name in your select to UPPERCASE so it wouldn’t fail on the insert, e.g.
      select * FROM SCOTT.EMP –> insert into “SCOTT.EMP” …

        1. thatjeffsmith Post
  49. Hi Jeff, I have started to use sqldeveloper (3.1.07, Italian localization) just today and I was so lucky to come across your blog ๐Ÿ™‚
    Something odd happens with the “delimited” format: I set up “;” as a delimiter in the preferences (which is BTW the default delimiter for Excel in my case), but it gets ignored and the default delimiter (comma) is used.
    Thanks a lot for sharing all these tips!

    1. thatjeffsmith Post

      The hinted queries don’t follow the preferences – I’d call it a bug, but this is technically an undocumented feature. I’ve been bugging the developer to make it a full-featured workflow, and then I could log a bug on this ๐Ÿ™‚

      That being said, we’re working on the next version of SQL Developer now and maybe we can work something in for you!

  50. Indeed. An unexpected gotcha since I am a local administrator at my corporate workstation. There’s obviously more to Windows 7’s permissions model than I thought. Thanks for your help Jeff.

  51. Thanks Jeff. That’s no different from the advice at the OTN site, but what I have since discovered after a bit more work was that I didn’t have sufficient privileges to create a file in the places I tested, eg, C:\

    Bottom line: it works (in places where you have sufficient privileges to read/write files).

    1. thatjeffsmith Post
  52. Thank you so much for this time saver. Consider my behaviour happily changed.

    I’d like to simplify my life further though: How do I go straight from F5 to output file, i.e., bypass the copy-paste required when using the Script Output window. I ask a similar question in the OTN forums where you mentioned using SPOOL. My thanks.

    1. thatjeffsmith Post

      Yes, the SPOOL should work.

      set echo on
      spool c:\spool_text_hint.log

      select /*csv*/ * from scott.emp;

      Contents of the .log file
      > select /*csv*/ * from scott.emp
      9999,”ADAMS”,”CLERK”,7788,23-MAY-87 12.00.00,1100,,20
      7369,”SMITH”,”CLERK”,7902,17-DEC-80 12.00.00,800,,20
      7499,”ALLEN”,”SALESMAN”,7698,20-FEB-81 12.00.00,1600,300,30
      7521,”WARD”,”SALESMAN”,7698,22-FEB-81 12.00.00,1250,500,30
      7566,”JONES”,”MANAGER”,7839,02-APR-81 12.00.00,2975,,20
      7654,”MARTIN”,”SALESMAN”,7698,28-SEP-81 12.00.00,1250,1400,30
      7698,”BLAKE”,”MANAGER”,7839,01-MAY-81 12.00.00,2850,,30
      7782,”CLARK”,”MANAGER”,7839,09-JUN-81 12.00.00,2450,,10
      7788,”SCOTT”,”ANALYST”,7566,19-APR-87 12.00.00,3000,,20
      7839,”KING”,”PRESIDENT”,,17-NOV-81 12.00.00,5000,,10
      7844,”TURNER”,”SALESMAN”,7698,08-SEP-81 12.00.00,1500,0,30
      7876,”ADAMS”,”CLERK”,7788,23-MAY-87 12.00.00,1100,,20
      7900,”JAMES”,”CLERK”,7698,03-DEC-81 12.00.00,950,,30
      7902,”FORD”,”ANALYST”,7566,03-DEC-81 12.00.00,3000,,20
      7934,”MILLER”,”CLERK”,7782,23-JAN-82 12.00.00,1300,,10

      1. Great tip Jeff! I was exporting the results from the graphic grid, but that does not work for really big result sets.

        When I try SPOOL, I’m only getting the SQL in the output file, not the results. Any ideas?

        Thank you!

        1. thatjeffsmith Post

          You’re probably running out of memory when you’re doing the Excel export. Switch the method to CSV – will run much, much faster.

          For SPOOL, I ran this in as worksheet using v3.2.2

          spool c:\demo_spool.txt

          SELECT *
          FROM scott.emp
          WHERE 1=1 AND
          empno > 256;

          And here’s the output of the file –

          > SELECT *
          FROM scott.emp
          WHERE 1=1 AND
          empno > 256
          ———- ———- ——— ———- ——————– ———- ———- ———-
          7369 SMITH CLERK 7902 17-DEC-1980 12.00.00 800 20
          7499 ALLEN SALESMAN 7698 20-FEB-1981 12.00.00 1600 300 30
          7521 WARD SALESMAN 7698 22-FEB-1981 12.00.00 1250 500 30
          7566 JONES MANAGER 7839 02-APR-1981 12.00.00 2975 20
          7654 MARTIN SALESMAN 7698 28-SEP-1981 12.00.00 1250 1400 30
          7698 BLAKE MANAGER 7839 01-MAY-1981 12.00.00 2850 30
          7782 CLARK MANAGER 7839 09-JUN-1981 12.00.00 2450 10
          7788 SCOTT ANALYST 7566 19-APR-1987 12.00.00 3000 20

  53. Hi Jeff,

    I dont know if you can help me, but I am new using Oracle Developer (using Version 3.1.06)

    My baby have pressed some keys and now I have all the char’s visible like the CR and line feed, tabs, spaces, etc…

    can you please help me out to remove this?

    thanks in advanced!!!

    1. thatjeffsmith Post

      First of all, that’s pretty awesome. I think your baby has a strong future in IT in front of them ๐Ÿ™‚

      There’s an option in the Preferences dialog to show these characters. Check:

      Preferences > Code Editor > Display > Show Whitespace

    1. thatjeffsmith Post
      1. Yes I am.I’m only able to get the export menu when I create a report based on the initial query,and then execute that report.By simply running the query using F5 doesn’t bring up the “spreadsheet like” workbook from which you can do the export.

        1. thatjeffsmith Post

          I’m confused. If you want to use the /*csv*/ trick – there is no Export menu. It just spits the results out in a comma delimited format. You then use copy and paste to get the records out of SQL Developer. So saying this doesn’t work and then mentioning the export or ‘spreadsheet like’ workbook is confusing me on exactly what you’re trying to accomplish.

          Maybe if you provided a screenshot or an exact list of 1..2..3..n steps, I could give you better advice?

  54. I’m new at Oracle SQL developer. I can get the above to work using the F5. Data is just how I need it, but I’m not sure how to save that output. I want to save it as a .csv.
    I’m using Oracle SQL Developer v3.0.04.

    1. thatjeffsmith Post

      You could hit the ‘Save’ button in the script output toolbar, or just copy and paste the output to the editor/program of your choice.

      If you want a more user friendly method to handle saving your query output, just run the query without the hint, then right-click in a the results grid, and choose ‘Export.’ Although in your version if may say ‘Unload.’ Either way, you’ll get a wizard to setup your output for the format of your choice.

  55. No way!! This is great. Is there a way to do it without the quotation marks? This is done in the wizard by changing the Left Enclosure (and Right Enclosure).

    1. thatjeffsmith Post
      1. Is there any progress on that? Quotation marks in /*text*/ export is the only thing preventing me from auto-generating half of my routine PL/SQL code. It would actually be enough if those hints used the default preferences under Database->Utilities->Export instead of overriding them.

        1. thatjeffsmith Post
          1. Sorry to dig up an old post, but I’ve just run into this same issue. I am on Build MAIN-12.84 and when I use this awesome feature, the text output still has double quotes (“) in it. Has this been resolved in later versions?

          2. thatjeffsmith Post

            Hmmm. Resolved infers that there is an actual issue or bug, yes?

            This is just the default behavior. The formatter hints take the defaults, and the default is to quote strings. You can turn that off, but it won’t affect the /*csv*/ hint – but the grid > export > CSV will honor it.

    1. JeffS Post
    1. JeffS Post
    1. JeffS Post

      Thanks for sharing that Kent! I consider this the highest form of praise a software user can offer – change in behavior. I’ll let @krisrice know you approve ๐Ÿ™‚ BTW, we’ll have to meet proper in San Antonio next month!

Leave a Reply

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