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
I am using the latest version (4.0.0.13 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(Array.java:70)
etc
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?
I’ve noticed you posted the same question on the forums – I’d suggest we continue the conversation there, but…
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.
Try the query again, but only a few rows, does that work?
Jeff, is this support extensible at all? Could someone write their own formatter that output something different such as c# or java objects?
No, but give me an example of how this would format for ‘java’ and maybe we can add it for you 🙂
Jeff, any idea if they plan on adding /*json*/ ?
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 🙂
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.
Gotcha. You now about our APEX Listener, yes? It will auto-serve up JSON for your SQL queries assuming you set your RESTful services.
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.
In the latest version (4.0.0.13 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
Feature hasn’t been disabled, it’s a bug. The bug has been fixed for the production release.
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. 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?
Thanks,
Jeff
Unfortunately this no longer works for me since upgrading to 3.2.2.
Any idea how to get it to work again?
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.
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.
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.
Actually that works for me. Just put the hint in the SELECT, not the WITH
[sql]
WITH q AS
(SELECT owner,
table_name,
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 ()
||’:100:400:’
|| last_analyzed last_stats_chart,
owner,
table_name,
ROUND(last_analyzed) days_since_last_stats
FROM q;
[/sql]
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?
That code is assuming there’s only one query set, hence the multiple search forms. I think you’ll have to manually edit out the additional search forms from the resulting HTML code.
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/password@db @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?
Thanks!
The hints mentioned in this post are SQL Developer specific, so if you use out in sqlplus, it will be ignored.
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
,field2
,field3
,field4
,field5
–etc
FROM
(SELECT ‘field1’ …FROM…)
order by field2, field3;
Spool off
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.
Thanks.
Correct.
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.
Thank you for your help Jeff, I will look into Tom Kyte’s solution.
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 value..is there any macro we can use in sqlo developer…
thanq
Have you seen this?
http://oracledeli.wordpress.com/2011/09/28/sql-developer_using_macros/
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.
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?
We don’t have a command-line interface to surface script or query execution…yet.
Way cool, and I totally agree with your assessment of developers who use their own tools. Thanks for pointing me in the right direction here.
Thanks Gaffi. Appreciate you sharing your experience here and the encouragement.
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?
I just tried this and got something different…
select /*insert*/ * from scott.emp;
REM INSERTING into scott.emp
SET DEFINE OFF;
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” …
Thanks very much, that did the trick! Is it because Oracle is casting identifiers?
It’s not my code, so I’d only be guessing as to the cause.
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!
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!
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.
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).
As expected, yes?
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. http://is.gd/IdC5CY My thanks.
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
“EMPNO”,”ENAME”,”JOB”,”MGR”,”HIREDATE”,”SAL”,”COMM”,”DEPTNO”
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
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!
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——————– ———- ———- ———-
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
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!!!
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
Not working for me even though I’m using Sql Dev version 3.1.7.
I’ve seen it act up before, but an application restart always fixed it. Can you confirm you’re executing the statement using F5 (script execution)?
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.
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?
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.
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.
I gave this a quick write up on my own blog so that I won’t forget this. 🙂 http://robertmarkbramprogrammer.blogspot.com.au/2012/06/query-results-as-csv-in-oracle-sql.html
Thanks Jeff – this is brilliant. 🙂
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).
There should be, but it’s not ‘working.’ Let me go talk to the head lazy dev 😉
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.
I think that’s where the feature needs to go, but for now it remains ‘undocumented.’ You could submit a request to the Exchanage and try to get it fast-tracked.
Sorry to dig up an old post, but I’ve just run into this same issue. I am on 4.0.0.12 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?
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.
Use /*TEXT*/ over /*text*/ and the surrounding double quotes disappear. I know it seems trivial, but it worked for me.
Thanks…very cool! 8^)
Thanks Ed!
Superb Jeff, thanks for the very useful post. I have now updated my answer on StackOverflow to include your method.
http://stackoverflow.com/questions/4168398/how-to-export-query-result-to-csv-in-oracle-sql-developer/4169011#4169011
Regards
Ian
Wow, thanks Ian! Btw, Ian also happens to be my son’s name 🙂
Very cool! I just used the export tool earlier today. Now I can skip all those set up steps.
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!