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 –
And the ‘new’ way (well, new to me!) –
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!
203 Comments
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.
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!
Asking questions are really good thing if you are not understanding something completely,
but this article presents nice understanding yet.
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.
I would like to use the SELECT /*csv*/ * from yada yada yada; with a DISTINCT command. Is that supported? Running 4.0.1.14
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
however it works on my workstation which has v4.0.1.14.48 installed….
I think we broke that in EA1. It’s fixed in EA2 and for final 4.1 release..available soon.
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!
Nice, thanks!
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
Hmm, no I haven’t seen that. What version of SQL Developer are you using?
My “About” dialog says ver 1.5.5, build main-5969.
That version is about 10 years old 🙂
No way! Well, I guess I had better upgrade. What version are you on, Jeff?
4.0.3 and 4.1EA2 and about 17 different development builds – but you can’t have those 🙂
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.
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
There’s a bug with EA1 and the export format comments. It’s fixed in 4.1 for our EA2 drop – which is coming soon!
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
They got busted in the EA. It’s fixed for version 4.1 and there’s a new one for you as well coming – /*json*/ 🙂
[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;
Yup, just remember that’s new for 4.1 and it will continue to print in HTML format until you unset sqlformat.
json will certainly be nifty, won’t complain at a simple method to generate json!
That’s my thought too, nice and easy way to create some test 1’s and 0’s 🙂
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?
I am now die hard fan of sqldeveloper!!
Thanks for sharing that Amit! We’ll take as many of those as we can get 🙂
I’ll check out the link – thanks!
I have tried linesize 30000 and I got: warning: linesize set to 2000 maximum (32767 specified)
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?
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.
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!
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.
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)
One example from Tim Hall AKA @oraclebase
From the docs – You can define LINESIZE as a value from 1 to a maximum that is system dependent. You should be able to go WAAAAY higher than 2000.
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!
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?
Thank you! The company where I work have the old version installed because administrative issues.
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.
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…
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.
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
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)
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
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.
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
Help > About
It probably won’t help, but we do have a .1 and a .2 patch available as well.
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
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
🙂
They work for me, but I’m in 4.0.1…so you could try that. What version of the JDK are you running?
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;
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.
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.
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.
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.
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!
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!
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]
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.
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)
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
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.
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:
