Formatting Query Results to CSV in Oracle SQL Developer

thatjeffsmith SQL Developer 200 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!

Related Posts

Tell Others About This Story:

Comments 200

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

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

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

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

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

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

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

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

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author
    3. thatjeffsmith Post
      Author
  8. 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.

  9. 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 4.1.0.17 (MAIN 17.29)
    – Java 8.0_31

    The output isn’t formatted .. Any idea ?

    http://snag.gy/aPGYh.jpg

    Thx a lot

    1. thatjeffsmith Post
      Author
  10. 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 4.1.0.17?
    I could reproduce all of your examples with version 4.0.2.15 but not with 4.1.0.17

    1. thatjeffsmith Post
      Author
    2. [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;

    3. thatjeffsmith Post
      Author
    4. thatjeffsmith Post
      Author
  11. Hi Jeff,

    I ran this script:

    SET HEADING OFF ECHO OFF
    spool datefromdual.csv
    select /*delimited*/ sysdate from dual;
    spool off;
    /

    and got this output in the csv file:

    > select /*delimited*/ sysdate from dual
    SYSDATE
    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
      Author
  12. I’ll check out the link – thanks!

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

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

      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.

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

    3. thatjeffsmith Post
      Author

      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.

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

    5. thatjeffsmith Post
      Author
  14. 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
      Author

      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?

    2. Thank you! The company where I work have the old version installed because administrative issues.

    3. thatjeffsmith Post
      Author

      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.

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

      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.

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

      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)

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

    Regards,
    Rakesh

    1. thatjeffsmith Post
      Author

      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.

    2. Sql developer Version 4.0.0.13, 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.

      Regards,
      Rakesh

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

  18. The hints are not working in version: 4.0.0.13.30
    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
      Author
  19. 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 3.2.10.09. 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 OFF;

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

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

    1. thatjeffsmith Post
      Author

      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.

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

    3. thatjeffsmith Post
      Author

      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.

    4. thatjeffsmith Post
      Author

      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.

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

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

      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?

      [sql]
      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;
      [/sql]

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

      Sounds like you might need to write a custom formatter to get exactly what you want..or raise an issue with Support or our Exchange (sqldeveloper.oracle.com)

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

    Thanks.
    Greg

    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 “~”.
      100000***0030~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~0047RG
      100001***0002JAVA~PORT~Account~1111~Test~~~0001EQ

      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.

      Thanks.

    2. thatjeffsmith Post
      Author

      If i run

      [sql]
      select
      FIRST_NAME ,
      LAST_NAME ,
      SALARY ,
      COMMISSION_PCT ,
      DEPARTMENT_ID from HR.EMPLOYEES;
      [/sql]

      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

Leave a Reply

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