0

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.

Improve this question
5
  • Please edit the question to include a minimal reproducible example with: the CREATE TABLE statement for your tables; the INSERT statement for some sample data; the expected output for that sample data; YOUR attempt at a solution; details of the application you are using to connect to the database (do you want a pure PL/SQL solution or an SQL/Plus script to SPOOL the output or a SQL Developer solution or something else?) Commented Jan 25, 2022 at 9:16
  • @MT0 Thanks for reply but I used spool which is not working here each table having many columns but i have to select just few columns in above SQL query. Commented Jan 25, 2022 at 9:22
  • What tool are you using ? With sqldeveloper and sqlcl export to csv is a breeze. Commented Jan 25, 2022 at 9:45
  • @KoenLostrie Toad I am using Commented Jan 25, 2022 at 9:49
  • 1
    Might be worth installing sqlcl for this purpose only - it will make your life easier, see answer below. Commented Jan 25, 2022 at 9:52

1 Answer 1

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for the answer but I am not able to see csv file where it saved in my local directory.
This was just an example of a query generating csv output. To spool to a file, user the spool command. Updated my answer.
In TOAD it is not working. I am not using in SQL command prompt. I am using SQL editor. So could you please let me know how to do it?
This solution is specific to sqlcl, a free tool offered by Oracle. In sqldeveloper you can also export as csv but there it is manual. I'm not familiar with Toad.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.