ThatJeffSmith

Formatting Query Results to CSV in Oracle SQL Developer

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 Plugin for WordPress, Blogger...
Tell Others About This Story:

Similar Posts by Content Area: , , , ,