May 2, 2012

Formatting Query Results to CSV in Oracle SQL Developer

I find that programs that are developed by the same folks that use that program in production are some of the best applications out there. The developer is the user. They have to eat their own dog food – an unpleasant metaphor, but one that’s pretty well understood. Two great examples of that here are APEX and of course SQL Developer.

Of course not every dog gets to add his own secret ingredients to his dinner!

I also find that lazy developers make the best developers. They are so lazy that they will spend a few extra minutes to write a program that writes their programs for them. And so you’ll find this cool kind of stuff all over the application.

Here’s an example of that in SQL Developer -

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.

You can try XLSX if you want, but I’m not sure how useful the output will be.

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


> SELECT /*csv*/ * FROM scott.emp
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
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

> SELECT /*xml*/ * FROM scott.emp
<?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-80 12.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-81 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[1600]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[300]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7521]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[WARD]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[SALESMAN]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[22-FEB-81 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[1250]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[500]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7566]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[JONES]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[MANAGER]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7839]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[02-APR-81 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[2975]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7654]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[MARTIN]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[SALESMAN]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[28-SEP-81 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[1250]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[1400]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7698]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[BLAKE]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[MANAGER]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7839]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[01-MAY-81 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[2850]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7782]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[CLARK]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[MANAGER]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7839]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[09-JUN-81 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[2450]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[10]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7788]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[SCOTT]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[ANALYST]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7566]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[19-APR-87 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[3000]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7839]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[KING]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[PRESIDENT]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[17-NOV-81 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[5000]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[10]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7844]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[TURNER]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[SALESMAN]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[08-SEP-81 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[1500]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[0]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7876]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[ADAMS]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7788]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[23-MAY-87 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[1100]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7900]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[JAMES]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[03-DEC-81 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[950]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7902]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[FORD]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[ANALYST]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7566]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[03-DEC-81 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[3000]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="EMPNO"><![CDATA[7934]]></COLUMN>
		<COLUMN NAME="ENAME"><![CDATA[MILLER]]></COLUMN>
		<COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN>
		<COLUMN NAME="MGR"><![CDATA[7782]]></COLUMN>
		<COLUMN NAME="HIREDATE"><![CDATA[23-JAN-82 12.00.00]]></COLUMN>
		<COLUMN NAME="SAL"><![CDATA[1300]]></COLUMN>
		<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
		<COLUMN NAME="DEPTNO"><![CDATA[10]]></COLUMN>
	</ROW>
</RESULTS>
> SELECT /*html*/ * FROM scott.emp
<html><head>
<meta http-equiv="content-type" content="text/html; charset=UTF8">
<!-- base href="http://apexdev.us.oracle.com:7778/pls/apx11w/" -->
<style type="text/css">
table {
background-color:#F2F2F5;
border-width:1px 1px 0px 1px;
border-color:#C9CBD3;
border-style:solid;
}
td {
color:#000000;
font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;
font-size:9pt;
background-color:#EAEFF5;
padding:8px;
background-color:#F2F2F5;
border-color:#ffffff #ffffff #cccccc #ffffff;
border-style:solid solid solid solid;
border-width:1px 0px 1px 0px;
}
th {
font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif;
font-size:9pt;
padding:8px;
background-color:#CFE0F1;
border-color:#ffffff #ffffff #cccccc #ffffff;
border-style:solid solid solid none;
border-width:1px 0px 1px 0px;
white-space:nowrap;
}
</style>
<script type="text/javascript">
window.apex_search = {};
apex_search.init = function (){
	this.rows = document.getElementById('data').getElementsByTagName('TR');
	this.rows_length = apex_search.rows.length;
	this.rows_text =  [];
	for (var i=0;i<apex_search.rows_length;i++){
        this.rows_text[i] = (apex_search.rows[i].innerText)?apex_search.rows[i].innerText.toUpperCase():apex_search.rows[i].textContent.toUpperCase();
	}
	this.time = false;
}

apex_search.lsearch = function(){
	this.term = document.getElementById('S').value.toUpperCase();
	for(var i=0,row;row = this.rows[i],row_text = this.rows_text[i];i++){
		row.style.display = ((row_text.indexOf(this.term) != -1) || this.term  === '')?'':'none';
	}
	this.time = false;
}

apex_search.search = function(e){
    var keycode;
    if(window.event){keycode = window.event.keyCode;}
    else if (e){keycode = e.which;}
    else {return false;}
    if(keycode == 13){
		apex_search.lsearch();
	}
    else{return false;}
}</script>
</head><body onload="apex_search.init();">
<table border="0" cellpadding="0" cellspacing="0">
<tbody><tr><td><input type="text" size="30" maxlength="1000" value="" id="S" onkeyup="apex_search.search(event);" /><input type="button" value="Search" onclick="apex_search.lsearch();"/> 
</td></tr>
</tbody></table>
<br>
<table border="0" cellpadding="0" cellspacing="0">
<tr>	<th>EMPNO</th>
	<th>ENAME</th>
	<th>JOB</th>
	<th>MGR</th>
	<th>HIREDATE</th>
	<th>SAL</th>
	<th>COMM</th>
	<th>DEPTNO</th>
</tr>
<tbody id="data">

	<tr>
<td align="right">7369</td>
<td>SMITH</td>
<td>CLERK</td>
<td align="right">7902</td>
<td>17-DEC-80 12.00.00</td>
<td align="right">800</td>
<td align="right">&nbsp;</td>
<td align="right">20</td>
	</tr>
	<tr>
<td align="right">7499</td>
<td>ALLEN</td>
<td>SALESMAN</td>
<td align="right">7698</td>
<td>20-FEB-81 12.00.00</td>
<td align="right">1600</td>
<td align="right">300</td>
<td align="right">30</td>
	</tr>
	<tr>
<td align="right">7521</td>
<td>WARD</td>
<td>SALESMAN</td>
<td align="right">7698</td>
<td>22-FEB-81 12.00.00</td>
<td align="right">1250</td>
<td align="right">500</td>
<td align="right">30</td>
	</tr>
	<tr>
<td align="right">7566</td>
<td>JONES</td>
<td>MANAGER</td>
<td align="right">7839</td>
<td>02-APR-81 12.00.00</td>
<td align="right">2975</td>
<td align="right">&nbsp;</td>
<td align="right">20</td>
	</tr>
	<tr>
<td align="right">7654</td>
<td>MARTIN</td>
<td>SALESMAN</td>
<td align="right">7698</td>
<td>28-SEP-81 12.00.00</td>
<td align="right">1250</td>
<td align="right">1400</td>
<td align="right">30</td>
	</tr>
	<tr>
<td align="right">7698</td>
<td>BLAKE</td>
<td>MANAGER</td>
<td align="right">7839</td>
<td>01-MAY-81 12.00.00</td>
<td align="right">2850</td>
<td align="right">&nbsp;</td>
<td align="right">30</td>
	</tr>
	<tr>
<td align="right">7782</td>
<td>CLARK</td>
<td>MANAGER</td>
<td align="right">7839</td>
<td>09-JUN-81 12.00.00</td>
<td align="right">2450</td>
<td align="right">&nbsp;</td>
<td align="right">10</td>
	</tr>
	<tr>
<td align="right">7788</td>
<td>SCOTT</td>
<td>ANALYST</td>
<td align="right">7566</td>
<td>19-APR-87 12.00.00</td>
<td align="right">3000</td>
<td align="right">&nbsp;</td>
<td align="right">20</td>
	</tr>
	<tr>
<td align="right">7839</td>
<td>KING</td>
<td>PRESIDENT</td>
<td align="right">&nbsp;</td>
<td>17-NOV-81 12.00.00</td>
<td align="right">5000</td>
<td align="right">&nbsp;</td>
<td align="right">10</td>
	</tr>
	<tr>
<td align="right">7844</td>
<td>TURNER</td>
<td>SALESMAN</td>
<td align="right">7698</td>
<td>08-SEP-81 12.00.00</td>
<td align="right">1500</td>
<td align="right">0</td>
<td align="right">30</td>
	</tr>
	<tr>
<td align="right">7876</td>
<td>ADAMS</td>
<td>CLERK</td>
<td align="right">7788</td>
<td>23-MAY-87 12.00.00</td>
<td align="right">1100</td>
<td align="right">&nbsp;</td>
<td align="right">20</td>
	</tr>
	<tr>
<td align="right">7900</td>
<td>JAMES</td>
<td>CLERK</td>
<td align="right">7698</td>
<td>03-DEC-81 12.00.00</td>
<td align="right">950</td>
<td align="right">&nbsp;</td>
<td align="right">30</td>
	</tr>
	<tr>
<td align="right">7902</td>
<td>FORD</td>
<td>ANALYST</td>
<td align="right">7566</td>
<td>03-DEC-81 12.00.00</td>
<td align="right">3000</td>
<td align="right">&nbsp;</td>
<td align="right">20</td>
	</tr>
	<tr>
<td align="right">7934</td>
<td>MILLER</td>
<td>CLERK</td>
<td align="right">7782</td>
<td>23-JAN-82 12.00.00</td>
<td align="right">1300</td>
<td align="right">&nbsp;</td>
<td align="right">10</td>
	</tr>
</tbody></table><!-- SQL:
null--></body></html>
> SELECT /*delimited*/ * FROM scott.emp
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
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

> SELECT /*insert*/ * FROM scott.emp
REM INSERTING into scott.emp
SET DEFINE OFF;
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80 12.00.00','DD-MON-RR HH.MI.SS'),800,null,20);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81 12.00.00','DD-MON-RR HH.MI.SS'),1600,300,30);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81 12.00.00','DD-MON-RR HH.MI.SS'),1250,500,30);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81 12.00.00','DD-MON-RR HH.MI.SS'),2975,null,20);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81 12.00.00','DD-MON-RR HH.MI.SS'),1250,1400,30);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81 12.00.00','DD-MON-RR HH.MI.SS'),2850,null,30);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81 12.00.00','DD-MON-RR HH.MI.SS'),2450,null,10);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87 12.00.00','DD-MON-RR HH.MI.SS'),3000,null,20);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81 12.00.00','DD-MON-RR HH.MI.SS'),5000,null,10);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81 12.00.00','DD-MON-RR HH.MI.SS'),1500,0,30);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87 12.00.00','DD-MON-RR HH.MI.SS'),1100,null,20);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81 12.00.00','DD-MON-RR HH.MI.SS'),950,null,30);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81 12.00.00','DD-MON-RR HH.MI.SS'),3000,null,20);
Insert into scott.emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82 12.00.00','DD-MON-RR HH.MI.SS'),1300,null,10);

> SELECT /*loader*/ * FROM scott.emp
 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|

> SELECT /*fixed*/ * FROM scott.emp
"EMPNO"                       "ENAME"                       "JOB"                         "MGR"                         "HIREDATE"                    "SAL"                         "COMM"                        "DEPTNO"                      
"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"                          

> SELECT /*text*/ * FROM scott.emp
"EMPNO"null"ENAME"null"JOB"null"MGR"null"HIREDATE"null"SAL"null"COMM"null"DEPTNO"
7369null"SMITH"null"CLERK"null7902null17-DEC-80 12.00.00null800nullnull20
7499null"ALLEN"null"SALESMAN"null7698null20-FEB-81 12.00.00null1600null300null30
7521null"WARD"null"SALESMAN"null7698null22-FEB-81 12.00.00null1250null500null30
7566null"JONES"null"MANAGER"null7839null02-APR-81 12.00.00null2975nullnull20
7654null"MARTIN"null"SALESMAN"null7698null28-SEP-81 12.00.00null1250null1400null30
7698null"BLAKE"null"MANAGER"null7839null01-MAY-81 12.00.00null2850nullnull30
7782null"CLARK"null"MANAGER"null7839null09-JUN-81 12.00.00null2450nullnull10
7788null"SCOTT"null"ANALYST"null7566null19-APR-87 12.00.00null3000nullnull20
7839null"KING"null"PRESIDENT"nullnull17-NOV-81 12.00.00null5000nullnull10
7844null"TURNER"null"SALESMAN"null7698null08-SEP-81 12.00.00null1500null0null30
7876null"ADAMS"null"CLERK"null7788null23-MAY-87 12.00.00null1100nullnull20
7900null"JAMES"null"CLERK"null7698null03-DEC-81 12.00.00null950nullnull30
7902null"FORD"null"ANALYST"null7566null03-DEC-81 12.00.00null3000nullnull20
7934null"MILLER"null"CLERK"null7782null23-JAN-82 12.00.00null1300nullnull10

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!

Share on Tumblr

thatjeffsmith SQL Developer 53 csv, insert, sqldev, trick, worksheet

53 Comments

Kent Graziano
May 03, 2012 @ 16:42:40

Very cool! I just used the export tool earlier today. Now I can skip all those set up steps.

Reply
- JeffS
  May 03, 2012 @ 17:15:03
  
  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!
  
  Reply
Ian Carpenter
May 05, 2012 @ 15:40:31

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

Reply
- JeffS
  May 05, 2012 @ 15:57:55
  
  Wow, thanks Ian! Btw, Ian also happens to be my son’s name
  
  Reply
Ed Christopher
May 18, 2012 @ 08:59:00

Thanks…very cool! 8^)

Reply
- JeffS
  May 18, 2012 @ 10:12:59
  
  Thanks Ed!
  
  Reply
Sarah Kelly
Jun 26, 2012 @ 09:49:20

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

Reply
- thatjeffsmith
  Jun 26, 2012 @ 10:33:15
  
  There should be, but it’s not ‘working.’ Let me go talk to the head lazy dev
  
  Reply
  - Type
    Oct 30, 2012 @ 06:26:55
    
    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.
    
    Reply
    - thatjeffsmith
      Oct 30, 2012 @ 09:02:46
      
      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.
      
      Reply
Robert Mark Bram
Jun 29, 2012 @ 01:49:00

Thanks Jeff – this is brilliant.

Reply
Robert Mark Bram
Jun 29, 2012 @ 01:52:04

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

Reply
ERIN
Aug 09, 2012 @ 12:01:16

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.

Reply
- thatjeffsmith
  Aug 09, 2012 @ 12:14:16
  
  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.
  
  Reply
jacque
Sep 05, 2012 @ 15:38:16

Not working for me even though I’m using Sql Dev version 3.1.7.

Reply
- thatjeffsmith
  Sep 05, 2012 @ 15:49:20
  
  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)?
  
  Reply
  - Jacque
    Sep 05, 2012 @ 18:49:06
    
    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.
    
    Reply
    - thatjeffsmith
      Sep 05, 2012 @ 19:11:57
      
      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?
      
      Reply
Vitor Santos
Sep 12, 2012 @ 20:25:45

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

Reply
- thatjeffsmith
  Sep 12, 2012 @ 21:03:56
  
  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
  
  Reply
Michael
Nov 12, 2012 @ 23:23:48

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.

Reply
- thatjeffsmith
  Nov 13, 2012 @ 09:10:27
  
  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
  
  Reply
  - patricia
    Feb 07, 2013 @ 11:25:30
    
    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!
    
    Reply
    - thatjeffsmith
      Feb 07, 2013 @ 11:41:59
      
      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
      
      Reply
Michael
Nov 13, 2012 @ 20:46: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).

Reply
- thatjeffsmith
  Nov 14, 2012 @ 03:38:10
  
  As expected, yes?
  
  Reply
Michael
Nov 14, 2012 @ 15:52:34

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.

Reply
Carla Bertinelli
Nov 23, 2012 @ 09:15:18

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!

Reply
- thatjeffsmith
  Nov 25, 2012 @ 10:15:04
  
  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!
  
  Reply
Peter Voss
Nov 28, 2012 @ 16:21:39

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?

Reply
- thatjeffsmith
  Nov 28, 2012 @ 16:41:15
  
  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” …
  
  Reply
  - Peter Voss
    Nov 28, 2012 @ 16:58:04
    
    Thanks very much, that did the trick! Is it because Oracle is casting identifiers?
    
    Reply
    - thatjeffsmith
      Nov 28, 2012 @ 21:02:08
      
      It’s not my code, so I’d only be guessing as to the cause.
      
      Reply
Gaffi
Jan 18, 2013 @ 16:02:04

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.

Reply
- thatjeffsmith
  Jan 18, 2013 @ 19:21:26
  
  Thanks Gaffi. Appreciate you sharing your experience here and the encouragement.
  
  Reply
csv
Jan 23, 2013 @ 13:06:31

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

Reply
- thatjeffsmith
  Jan 23, 2013 @ 14:17:49
  
  Have you seen this?
  http://oracledeli.wordpress.com/2011/09/28/sql-developer_using_macros/
  
  Reply
  - csv
    Jan 24, 2013 @ 00:28:17
    
    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.
    
    Reply
  - csv
    Jan 24, 2013 @ 05:20:44
    
    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?
    
    Reply
    - thatjeffsmith
      Jan 24, 2013 @ 07:50:43
      
      We don’t have a command-line interface to surface script or query execution…yet.
      
      Reply
abus
Feb 11, 2013 @ 05:04:43

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!

Reply
- thatjeffsmith
  Feb 11, 2013 @ 05:41:23
  
  The hints mentioned in this post are SQL Developer specific, so if you use out in sqlplus, it will be ignored.
  
  Reply
  - abus
    Feb 11, 2013 @ 06:31:00
    
    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
    
    Reply
  - abus
    Feb 11, 2013 @ 09:39:54
    
    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.
    
    Reply
    - thatjeffsmith
      Feb 11, 2013 @ 11:52:38
      
      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.
      
      Reply
      - abus
        Feb 13, 2013 @ 04:32:42
        
        Thank you for your help Jeff, I will look into Tom Kyte’s solution.
vanha
Feb 28, 2013 @ 11:10:07

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?

Reply
- thatjeffsmith
  Feb 28, 2013 @ 11:18:16
  
  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.
  
  Reply

Jay
Mar 22, 2013 @ 13:24:43

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.

thatjeffsmith
Mar 22, 2013 @ 15:48:07

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

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;

CH
Apr 15, 2013 @ 18:54:24

Unfortunately this no longer works for me since upgrading to 3.2.2.
Any idea how to get it to work again?

Reply
- thatjeffsmith
  Apr 16, 2013 @ 09:18:36
  
  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.
  
  Reply
  - CH
    Apr 16, 2013 @ 12:22:34
    
    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.
    
    Reply

Formatting Query Results to CSV in Oracle SQL Developer

Quick ResultSet Exports as Script Output

53 Comments

Leave a Reply Cancel