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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Aravind.

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

Last updated: May 19, 2021 - 3:47 am UTC

Version: 4.1

Viewed 100K+ times! This question is

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

Rating

  (13 ratings)

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

Comments

Extremely useful

Aravind Lekshminarayanan, September 08, 2016 - 4:22 pm UTC


tagging the file name

Aravind, October 06, 2016 - 2:45 pm UTC

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

Karthik, October 25, 2017 - 9:47 pm UTC

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

Karthik, October 26, 2017 - 1:58 pm UTC

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

Rajeshwaran Jeyabal, October 26, 2017 - 2:36 pm UTC

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
October 27, 2017 - 3:23 am UTC

Good point

Column names missing

Shri, November 29, 2017 - 10:04 pm UTC

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

shri, November 30, 2017 - 3:06 pm UTC

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

shri, November 30, 2017 - 4:34 pm UTC

Thank you. That is what I did. I wanted to know if there was a better way to do it, but thanks.
Connor McDonald
December 01, 2017 - 2:53 am UTC

Or just use SQLcl or SQLPlus 12.2

Plzz help!!

Saurav jain, April 24, 2018 - 4:16 pm UTC

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

Chris Saxon
April 30, 2018 - 9:46 am UTC

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

Useful and need to declare file_handle

Ekta, November 28, 2018 - 7:10 pm UTC

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

Connor McDonald
November 29, 2018 - 12:52 am UTC

Thanks - we've corrected it.

PL/SQL: Statement ignored

Nibhay, May 13, 2020 - 8:34 pm UTC

Hi

This script has been very useful, getting the following error even after declaring the variable.

SQL> sho errors
Errors for PROCEDURE WATERFORD_CSV_EXPORT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
40/11 PL/SQL: Statement ignored
42/15 PLS-00302: component 'DATE_WORKED' must be declared
SQL>


Below is the script i am executing:
create or replace procedure waterford_csv_export is
file_handle UTL_FILE.file_type;
EVT_MRC VARCHAR2(15 CHAR);
EVT_RTYPE VARCHAR2(4 CHAR);
EVT_CODE VARCHAR2(30 CHAR);
EVT_DESC VARCHAR2(80 CHAR);
EVT_OBJECT VARCHAR2(30 CHAR);
EVT_JOBTYPE VARCHAR2(8 CHAR);
EVT_PPM VARCHAR2(20 CHAR);
EVT_STATUS VARCHAR2(4 CHAR);
EVT_DUE DATE;
EVT_TARGET DATE;
EVT_REQUESTEND DATE;
DATE_WORKED DATE;
Booked_Hours NUMBER(3);
EVT_COMPLETED DATE;
Days_Open NUMBER(3);
Open_Over_30_Days VARCHAR2(30 CHAR);
Due VARCHAR2(30 CHAR);
Overdue VARCHAR2(8 CHAR);
Count_EVT_DESC_ NUMBER(4);
Count_EVT_REQUESTEND_ NUMBER(4);
Count_Overdue_ NUMBER(4);
Count_EVT_CODE_ NUMBER(4);
Count_Overdue_1 NUMBER(4);
Count_EVT_CODE_24 NUMBER(4);
Count_Overdue_125 NUMBER(4);
Count_EVT_CODE_26 NUMBER(4);
Count_Overdue_127 NUMBER(4);
begin
file_handle := utl_file.fopen('WATERFORD_CSV', 'waterford_export'||to_char(sysdate,'DDMONYYYY')||'.csv', 'w');
for rws in (
select "T0"."C0" "EVT_MRC", "T0"."C1" "EVT_RTYPE", "T0"."C2" "EVT_CODE", "T0"."C3" "EVT_DESC", "T0"."C4" "EVT_OBJECT", "T0"."C5" "EVT_JOBTYPE", "T0"."C6" "EVT_PPM", "T0"."C7" "EVT_STATUS", "T0"."C8" "EVT_DUE", "T0"."C9" "EVT_TARGET", "T0"."C10" "EVT_REQUESTEND", "T0"."C11" "Date_Worked", "T0"."C12" "Booked_Hours", "T0"."C13" "EVT_COMPLETED", "T0"."C14" "Days_Open", "T0"."C15" "Open_Over_30_Days", "T0"."C16" "Due", "T0"."C17" "Overdue", count("T0"."C18") over () "Count_EVT_DESC_", count("T0"."C19") over () "Count_EVT_REQUESTEND_", count("T0"."C20") over () "Count_Overdue_", count("T0"."C21") over () "Count_EVT_CODE_", count("T0"."C20") over () "Count_Overdue_1", count("T0"."C21") over (partition by "T0"."C0") "Count_EVT_CODE_24", count("T0"."C20") over (partition by "T0"."C0") "Count_Overdue_125", count("T0"."C21") over (partition by "T0"."C0", "T0"."C1") "Count_EVT_CODE_26", count("T0"."C20") over (partition by "T0"."C0", "T0"."C1") "Count_Overdue_127"
from (
select "R5EVENTS"."EVT_MRC" "C0", "R5EVENTS"."EVT_RTYPE" "C1", "R5EVENTS"."EVT_CODE" "C2", "R5EVENTS"."EVT_DESC" "C3", "R5EVENTS"."EVT_OBJECT" "C4", "R5EVENTS"."EVT_JOBTYPE" "C5", "R5EVENTS"."EVT_PPM" "C6", "R5EVENTS"."EVT_STATUS" "C7", "R5EVENTS"."EVT_DUE" "C8", "R5EVENTS"."EVT_TARGET" "C9", "R5EVENTS"."EVT_REQUESTEND" "C10", "R5BOOKEDHOURS"."BOO_DATE" "C11", R5REP.REPHOURS("R5EVENTS"."EVT_CODE") "C12", "R5EVENTS"."EVT_COMPLETED" "C13", trunc(TRUNC( CURRENT_DATE )) - trunc("R5EVENTS"."EVT_CREATED") "C14", case when trunc(TRUNC( CURRENT_DATE )) - trunc("R5EVENTS"."EVT_CREATED")>30 then 'Open Over 30 Days' else NULL end "C15", case when "R5EVENTS"."EVT_DUE"<=SYSDATE+7 then 'Due in Next 7 Days' else NULL end "C16", case when "R5EVENTS"."EVT_DUE"+1<=SYSDATE then 'Overdue' else NULL end "C17", min("R5EVENTS"."EVT_DESC") "C18", min("R5EVENTS"."EVT_REQUESTEND") "C19", min(case when "R5EVENTS"."EVT_DUE"+1<=SYSDATE then 'Overdue' else NULL end ) "C20", min("R5EVENTS"."EVT_CODE") "C21"
from "R5BOOKEDHOURS" "R5BOOKEDHOURS" LEFT OUTER JOIN "R5EVENTS" "R5EVENTS" on "R5BOOKEDHOURS"."BOO_EVENT"="R5EVENTS"."EVT_CODE"
where "R5EVENTS"."EVT_ORG"='WATERFORD' and "R5EVENTS"."EVT_RSTATUS" in ('R')
group by "R5EVENTS"."EVT_CODE", "R5EVENTS"."EVT_DESC", "R5EVENTS"."EVT_STATUS", "R5EVENTS"."EVT_MRC", "R5EVENTS"."EVT_OBJECT", "R5EVENTS"."EVT_RTYPE", "R5EVENTS"."EVT_PPM", "R5EVENTS"."EVT_DUE", R5REP.REPHOURS("R5EVENTS"."EVT_CODE"), "R5EVENTS"."EVT_JOBTYPE", "R5EVENTS"."EVT_TARGET", "R5EVENTS"."EVT_COMPLETED", "R5EVENTS"."EVT_REQUESTEND", case when trunc(TRUNC( CURRENT_DATE )) - trunc("R5EVENTS"."EVT_CREATED")>30 then 'Open Over 30 Days' else NULL end , case when "R5EVENTS"."EVT_DUE"<=SYSDATE+7 then 'Due in Next 7 Days' else NULL end , case when "R5EVENTS"."EVT_DUE"+1<=SYSDATE then 'Overdue' else NULL end , "R5BOOKEDHOURS"."BOO_DATE", trunc(TRUNC( CURRENT_DATE )) - trunc("R5EVENTS"."EVT_CREATED")) "T0"
order by "EVT_MRC" asc nulls last, "EVT_RTYPE" asc nulls last, "EVT_TARGET" asc nulls last
) loop utl_file.put_line(file_handle,
rws.EVT_MRC||','||rws.EVT_RTYPE||','||rws.EVT_CODE||','||rws.EVT_DESC||','||rws.EVT_OBJECT||','||rws.EVT_JOBTYPE||','||rws.EVT_PPM||','||rws.EVT_STATUS||','||rws.EVT_DUE||','||rws.EVT_TARGET||','||rws.EVT_REQUESTEND
||','||rws.Date_Worked ||','||rws.Booked_Hours||','||rws.EVT_COMPLETED||','||rws.Days_Open||','||rws.Open_Over_30_Days||','||rws.Due||','||rws.Overdue||','||rws.Count_EVT_DESC_||','||rws.Count_EVT_REQUESTEND_||','|| rws.Count_Overdue_||','||rws.Count_EVT_CODE_||','||rws.Count_Overdue_1||','||rws.Count_EVT_CODE_24||','||rws.Count_Overdue_125||','||rws.Count_EVT_CODE_26||','||rws.Count_Overdue_127
);
end loop;
utl_file.fclose(file_handle);
end waterford_csv_export;
/
Connor McDonald
May 14, 2020 - 4:09 am UTC

glad we could help

PL/SQL: Statement ignored

Nibhay, May 14, 2020 - 1:01 pm UTC

Hi

The below script is throwing an error , even after declaring the variable , can you please check and help me out what am i doing wrong?

SQL> sho errors
Errors for PROCEDURE WATERFORD_CSV_EXPORT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
40/11 PL/SQL: Statement ignored
42/15 PLS-00302: component 'DATE_WORKED' must be declared
SQL>


Below is the script i am executing:
create or replace procedure waterford_csv_export is
file_handle UTL_FILE.file_type;
EVT_MRC VARCHAR2(15 CHAR);
EVT_RTYPE VARCHAR2(4 CHAR);
EVT_CODE VARCHAR2(30 CHAR);
EVT_DESC VARCHAR2(80 CHAR);
EVT_OBJECT VARCHAR2(30 CHAR);
EVT_JOBTYPE VARCHAR2(8 CHAR);
EVT_PPM VARCHAR2(20 CHAR);
EVT_STATUS VARCHAR2(4 CHAR);
EVT_DUE DATE;
EVT_TARGET DATE;
EVT_REQUESTEND DATE;
DATE_WORKED DATE;
Booked_Hours NUMBER(3);
EVT_COMPLETED DATE;
Days_Open NUMBER(3);
Open_Over_30_Days VARCHAR2(30 CHAR);
Due VARCHAR2(30 CHAR);
Overdue VARCHAR2(8 CHAR);
Count_EVT_DESC_ NUMBER(4);
Count_EVT_REQUESTEND_ NUMBER(4);
Count_Overdue_ NUMBER(4);
Count_EVT_CODE_ NUMBER(4);
Count_Overdue_1 NUMBER(4);
Count_EVT_CODE_24 NUMBER(4);
Count_Overdue_125 NUMBER(4);
Count_EVT_CODE_26 NUMBER(4);
Count_Overdue_127 NUMBER(4);
begin
file_handle := utl_file.fopen('WATERFORD_CSV', 'waterford_export'||to_char(sysdate,'DDMONYYYY')||'.csv', 'w');
for rws in (
select "T0"."C0" "EVT_MRC", "T0"."C1" "EVT_RTYPE", "T0"."C2" "EVT_CODE", "T0"."C3" "EVT_DESC", "T0"."C4" "EVT_OBJECT", "T0"."C5" "EVT_JOBTYPE", "T0"."C6" "EVT_PPM", "T0"."C7" "EVT_STATUS", "T0"."C8" "EVT_DUE", "T0"."C9" "EVT_TARGET", "T0"."C10" "EVT_REQUESTEND", "T0"."C11" "Date_Worked", "T0"."C12" "Booked_Hours", "T0"."C13" "EVT_COMPLETED", "T0"."C14" "Days_Open", "T0"."C15" "Open_Over_30_Days", "T0"."C16" "Due", "T0"."C17" "Overdue", count("T0"."C18") over () "Count_EVT_DESC_", count("T0"."C19") over () "Count_EVT_REQUESTEND_", count("T0"."C20") over () "Count_Overdue_", count("T0"."C21") over () "Count_EVT_CODE_", count("T0"."C20") over () "Count_Overdue_1", count("T0"."C21") over (partition by "T0"."C0") "Count_EVT_CODE_24", count("T0"."C20") over (partition by "T0"."C0") "Count_Overdue_125", count("T0"."C21") over (partition by "T0"."C0", "T0"."C1") "Count_EVT_CODE_26", count("T0"."C20") over (partition by "T0"."C0", "T0"."C1") "Count_Overdue_127"
from (
select "R5EVENTS"."EVT_MRC" "C0", "R5EVENTS"."EVT_RTYPE" "C1", "R5EVENTS"."EVT_CODE" "C2", "R5EVENTS"."EVT_DESC" "C3", "R5EVENTS"."EVT_OBJECT" "C4", "R5EVENTS"."EVT_JOBTYPE" "C5", "R5EVENTS"."EVT_PPM" "C6", "R5EVENTS"."EVT_STATUS" "C7", "R5EVENTS"."EVT_DUE" "C8", "R5EVENTS"."EVT_TARGET" "C9", "R5EVENTS"."EVT_REQUESTEND" "C10", "R5BOOKEDHOURS"."BOO_DATE" "C11", R5REP.REPHOURS("R5EVENTS"."EVT_CODE") "C12", "R5EVENTS"."EVT_COMPLETED" "C13", trunc(TRUNC( CURRENT_DATE )) - trunc("R5EVENTS"."EVT_CREATED") "C14", case when trunc(TRUNC( CURRENT_DATE )) - trunc("R5EVENTS"."EVT_CREATED")>30 then 'Open Over 30 Days' else NULL end "C15", case when "R5EVENTS"."EVT_DUE"<=SYSDATE+7 then 'Due in Next 7 Days' else NULL end "C16", case when "R5EVENTS"."EVT_DUE"+1<=SYSDATE then 'Overdue' else NULL end "C17", min("R5EVENTS"."EVT_DESC") "C18", min("R5EVENTS"."EVT_REQUESTEND") "C19", min(case when "R5EVENTS"."EVT_DUE"+1<=SYSDATE then 'Overdue' else NULL end ) "C20", min("R5EVENTS"."EVT_CODE") "C21"
from "R5BOOKEDHOURS" "R5BOOKEDHOURS" LEFT OUTER JOIN "R5EVENTS" "R5EVENTS" on "R5BOOKEDHOURS"."BOO_EVENT"="R5EVENTS"."EVT_CODE"
where "R5EVENTS"."EVT_ORG"='WATERFORD' and "R5EVENTS"."EVT_RSTATUS" in ('R')
group by "R5EVENTS"."EVT_CODE", "R5EVENTS"."EVT_DESC", "R5EVENTS"."EVT_STATUS", "R5EVENTS"."EVT_MRC", "R5EVENTS"."EVT_OBJECT", "R5EVENTS"."EVT_RTYPE", "R5EVENTS"."EVT_PPM", "R5EVENTS"."EVT_DUE", R5REP.REPHOURS("R5EVENTS"."EVT_CODE"), "R5EVENTS"."EVT_JOBTYPE", "R5EVENTS"."EVT_TARGET", "R5EVENTS"."EVT_COMPLETED", "R5EVENTS"."EVT_REQUESTEND", case when trunc(TRUNC( CURRENT_DATE )) - trunc("R5EVENTS"."EVT_CREATED")>30 then 'Open Over 30 Days' else NULL end , case when "R5EVENTS"."EVT_DUE"<=SYSDATE+7 then 'Due in Next 7 Days' else NULL end , case when "R5EVENTS"."EVT_DUE"+1<=SYSDATE then 'Overdue' else NULL end , "R5BOOKEDHOURS"."BOO_DATE", trunc(TRUNC( CURRENT_DATE )) - trunc("R5EVENTS"."EVT_CREATED")) "T0"
order by "EVT_MRC" asc nulls last, "EVT_RTYPE" asc nulls last, "EVT_TARGET" asc nulls last
) loop utl_file.put_line(file_handle,
rws.EVT_MRC||','||rws.EVT_RTYPE||','||rws.EVT_CODE||','||rws.EVT_DESC||','||rws.EVT_OBJECT||','||rws.EVT_JOBTYPE||','||rws.EVT_PPM||','||rws.EVT_STATUS||','||rws.EVT_DUE||','||rws.EVT_TARGET||','||rws.EVT_REQUESTEND
||','||rws.Date_Worked ||','||rws.Booked_Hours||','||rws.EVT_COMPLETED||','||rws.Days_Open||','||rws.Open_Over_30_Days||','||rws.Due||','||rws.Overdue||','||rws.Count_EVT_DESC_||','||rws.Count_EVT_REQUESTEND_||','|| rws.Count_Overdue_||','||rws.Count_EVT_CODE_||','||rws.Count_Overdue_1||','||rws.Count_EVT_CODE_24||','||rws.Count_Overdue_125||','||rws.Count_EVT_CODE_26||','||rws.Count_Overdue_127
);
end loop;
utl_file.fclose(file_handle);
end waterford_csv_export;
/
Chris Saxon
May 14, 2020 - 4:54 pm UTC

When you put something in double quotes, it becomes "Case Sensitive". Without the quotes it isn't.

For any quoted aliases, ensure you (double) quote them when referencing in put_line.

Result of String concatenation is too long

Sudhanshu, May 18, 2021 - 7:38 pm UTC

Hi, I have a where
1. the columns are changes regularly
2. There are 3 column with datatype as VARCHAR2(400 BYTE)
Hence instead of using direct column names I am using listaggregate function and then I get the error.
"Result of string concatenation is too long".

Could you suggest any workarounds
Connor McDonald
May 19, 2021 - 3:47 am UTC

That depends on version.

Natively we have this

https://connor-mcdonald.com/2019/02/05/listagg-hits-prime-time/

If you are pre-12.2, then you need to do it with SQL code eg

https://livesql.oracle.com/apex/livesql/file/content_EHSJPX1P6EEQ284YAX544WPXY.html

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library