Skip to Main Content
  • Questions
  • Automate the generation of SQL query output to CSV

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Aravind.

Asked: September 08, 2016 - 2:31 pm UTC

Answered by: Chris Saxon - Last updated: November 29, 2018 - 12:52 am UTC

Category: Developer - Version: 4.1

Viewed 50K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Advanced Replication

You Asked

Hello,

I have a SQL query which generates an output of nearly 200k records.
The need is to :
1) Generate the output of this query in text/csv format.
2) Schedule it to be run daily in the morning.
3) I have included select /*CSV*/ in the code.

So if you could please let me know what would the best possible approach to go about this it would be great.




and we said...

The CSV comment only works in SQL Developer unfortunately. The best way to handle this is to write a PL/SQL procedure that generates the file.

There's three things you'll need to create:

- A directory. This points the location on the database server you'll write the file to
- A procedure to write the file
- A scheduler job to automate the process

For example:

create or replace directory csv_dir as '/destination/for/csv';

create or replace procedure write_file is
  file_handle UTL_FILE.file_type;
begin
  file_handle := utl_file.fopen('CSV_DIR', 'csv_filename.csv', 'w', 32767);
  for rws in (
    select * from t -- your query here
  ) loop
    utl_file.put_line(file_handle, 
      rws.c1 || ',' || rws.c2 || ',' || rws.c3 -- your columns here
    );
  end loop;
  utl_file.fclose(file_handle);
end write_file;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'EXPORT_CSV_J',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin write_file; end;',
    number_of_arguments => 0,
    start_date => NULL,
    repeat_interval => 'FREQ=DAILY',
    end_date => NULL,
    enabled => FALSE,
    auto_drop => FALSE);

  DBMS_SCHEDULER.SET_ATTRIBUTE( 
    name => 'EXPORT_CSV_J', 
    attribute => 'logging_level', 
    value => DBMS_SCHEDULER.LOGGING_RUNS);
  
  DBMS_SCHEDULER.enable(
    name => 'EXPORT_CSV_J');
END;
/


You can find more examples of file writing routines at:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:88212348059
https://oracle-base.com/articles/9i/generating-csv-files

If you want to know more about scheduling when and how often the job runs, check this out:

https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse004.htm#i1023312

and you rated our response

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Extremely useful

September 08, 2016 - 4:22 pm UTC

Reviewer: Aravind Lekshminarayanan from Chicago


tagging the file name

October 06, 2016 - 2:45 pm UTC

Reviewer: Aravind from Chicago

Thanks for your response - it was extremely useful . Would it be possible to name the file based on Sysdate?
Eg: I would like to generate 2 files named as follows:
1)Test1Sep2016
2)Test2Aug2016

Chris Saxon

Followup  

October 07, 2016 - 3:11 am UTC

file_handle := utl_file.fopen('CSV_DIR', 'csv_filename'||to_char(sysdate,'MONYYYY')||'.csv', 'w', 32767);

writing SQL query output into .csv file

October 25, 2017 - 9:47 pm UTC

Reviewer: Karthik from Canada

Hi Tom,

We are unable to use UTL_FILE option to write into file due to database located on the client server.

Is there any other way I can write my SQL query output into .csv file without using UTL file.

Or
Extracting the oracle concurrent job output into .csv file through a job.

Thanks,
Karthik
Connor McDonald

Followup  

October 26, 2017 - 12:43 am UTC

Load up the 12.2 SQL Plus client on our machine.

Then you can "set markup csv"

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> set markup csv on
SQL> /

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,"17-DEC-80",800,,20
7499,"ALLEN","SALESMAN",7698,"20-FEB-81",1600,300,30
7521,"WARD","SALESMAN",7698,"22-FEB-81",1250,500,30
7566,"JONES","MANAGER",7839,"02-APR-81",2975,,20
7654,"MARTIN","SALESMAN",7698,"28-SEP-81",1250,1400,30
7698,"BLAKE","MANAGER",7839,"01-MAY-81",2850,,30
7782,"CLARK","MANAGER",7839,"09-JUN-81",2450,,10
7788,"SCOTT","ANALYST",7566,"09-DEC-82",3000,,20
7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10
7844,"TURNER","SALESMAN",7698,"08-SEP-81",1500,,30
7876,"ADAMS","CLERK",7788,"12-JAN-83",1100,,20
7900,"JAMES","CLERK",7698,"03-DEC-81",950,,30
7902,"FORD","ANALYST",7566,"03-DEC-81",3000,,20
7934,"MILLER","CLERK",7782,"23-JAN-82",1300,,10

14 rows selected.

October 26, 2017 - 1:58 pm UTC

Reviewer: Karthik

Thanks for your reply.

I'm using SQLPLUS 10.1.0.5.0 version, So I am not able to use that option.

Please guide me is there any other way I can extract the output in .csv.

If I use SPOOL option the job extracting the sql query and the result. How can I avoid SQL query in the output while doing SPOOL.

Thanks, Karthik
Connor McDonald

Followup  

October 26, 2017 - 2:00 pm UTC

I'm using SQLPLUS 10.1.0.5.0 version, So I am not able to use that option

No you *can* use the option of SQL Plus 12.2, you are *choosing* not to.

There is nothing to stop you from download the 12.2 software and running sqlplus 12.2 *just* on your machine.

SQLCL

October 26, 2017 - 2:36 pm UTC

Reviewer: Rajeshwaran Jeyabal

Other option would be to get SQLCL from sqldeveloper.oracle.com ( small utility with just of size less than 20MB, not install required ).

Then using that utility we can make use of SET SQLFORMAT CSV option to get the output in CSV format.

Something like this.
C:\Users\admin>type d:\script.sql
set heading off
set linesize 250
set long 9999
set feedback off
set sqlformat CSV
spool dept.lst
select * from dept;
spool off
exit

C:\Users\admin>dir dept.lst
 Volume in drive C is OS
 Volume Serial Number is D4D1-2052

 Directory of C:\Users\admin

File Not Found

C:\Users\admin>sql -S demo/demo@ora11g @d:\script.sql
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"


C:\Users\admin>type dept.lst
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"


C:\Users\admin>

Connor McDonald

Followup  

October 27, 2017 - 3:23 am UTC

Good point

Column names missing

November 29, 2017 - 10:04 pm UTC

Reviewer: Shri

Hi Tom, I used the procedure that you have with my query, but the output text file is missing column headers. Any way to get around that?

Thanks.
Chris Saxon

Followup  

November 30, 2017 - 2:07 pm UTC

It's Chris and Connor, but hey ;)

So what exactly did you do? Show us all the code you ran.

Followup-Shri

November 30, 2017 - 3:06 pm UTC

Reviewer: shri

Sorry Chris,
This is the part that is causing trouble I believe
For rws in (query)
LOOP
UTL_FILE.put_line (V_FILE_HANDLE,rws.COLNAME1 || '|' || rws.COLNAME2 || '|' || rws.COLNAME3||...ALL COLUMN NAMES);

END LOOP;
It gives me all the columns but not the column names and I'm trying to put this in a text file and not csv. Is it because of that?
Chris Saxon

Followup  

November 30, 2017 - 4:28 pm UTC

The example in the answer only writes the row data. If you want the column headings above this, you need to write them yourself first.

November 30, 2017 - 4:34 pm UTC

Reviewer: shri

Thank you. That is what I did. I wanted to know if there was a better way to do it, but thanks.
Connor McDonald

Followup  

December 01, 2017 - 2:53 am UTC

Or just use SQLcl or SQLPlus 12.2

Plzz help!!

April 24, 2018 - 4:16 pm UTC

Reviewer: Saurav jain from India

Plzz help me I am getting error in your provided code.
Plzz give me full code for 3 columns in table.

Chris Saxon

Followup  

April 30, 2018 - 9:46 am UTC

And the error you're getting is...?

Useful and need to declare file_handle

November 28, 2018 - 7:10 pm UTC

Reviewer: Ekta from Canada

You might have forgot to declare file_handle.
That gives an error.
file_handle UTL_FILE.file_type;

Connor McDonald

Followup  

November 29, 2018 - 12:52 am UTC

Thanks - we've corrected it.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.