CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
user8487380
user8487380

Reputation: 156

Script to extract header with data from oracle query & export into csv file

I am using one query as below. I want to export result set of below query into csv file.

select a.eid, b.ename, c.pid, b.pnm 
from tab1 a join tab2 b on a.eid=b.eid
join tab3 c on b.pid=c.pid;

I want to extract csv file from the above result set along with headers & save into C:\User\mows\ folder using Stored procedure or any script. So I need result set with header in csv like below format. Could you please help me on this.

EID     ENAME     PID    PNM
---     -----     ---    ---
1001    Steve     21     IT
1003    Tim       23     Management
1005    Craig     25     Account
.......................

Data along with headers in csv file when above SQL query run.

Upvotes: 0

Views: 1566

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18640

Here is one option using SQLcl - this is the command line client using the same engine as sqldeveloper. This allows passing hints in the sql query to format the output. For csv, this is a solution:

scott>select /*csv*/ * from emp;

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7839,"KING","PRESIDENT",,17/11/81,5001,,10
7698,"BLAKE","MANAGER",7839,01/05/81,2850,,30
7782,"CLARK","MANAGER",7839,09/06/81,2450,,10
7566,"JONES","MANAGER",7839,02/04/81,2975,,20
7788,"SCOTT","ANALYST",7566,09/12/82,3000,,20
7902,"FORD","ANALYST",7566,03/12/81,3000,,20
7369,"SMITH","CLERK",7902,17/12/80,801,,20
7499,"ALLEN","SALESMAN",7698,20/02/81,1600,300,30
7521,"WARD","SALESMAN",7698,22/02/81,1250,500,30
7654,"MARTIN","SALESMAN",7698,28/09/81,1250,1400,30
7844,"TURNER","SALESMAN",7698,08/09/81,1500,0,30
7876,"ADAMS","CLERK",7788,12/01/83,1100,,20
7900,"JAMES","CLERK",7698,03/12/81,950,,30
7934,"MILLER","CLERK",7782,23/01/82,1300,,10

14 rows selected.

To spool to a file, use the spool command, just like in sqlplus. Example for some selected columns spooled to a file:

scott>spool empcsv.csv
scott>select /*csv*/ empno, ename FROM emp FETCH FIRST 5 ROWS ONLY;

"EMPNO","ENAME"
7839,"KING"
7698,"BLAKE"
7782,"CLARK"
7566,"JONES"
7788,"SCOTT"

scott>spool off

Upvotes: 1

Related Questions