ThatJeffSmith

Formatting Query Results to CSV in Oracle SQL Developer

Social:

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):
[text collapse=”true”]

> 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
[/text]

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...
Social:

Similar Posts by Content Area: , , , ,