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
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
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
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>
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.
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?
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.
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.
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;
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;
/
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;
/
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
May 19, 2021 - 3:47 am UTC