Skip to Main Content
  • Questions
  • write data from a table to a csv file and save in it a directory.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Geetika .

Asked: October 13, 2016 - 4:32 am UTC

Last updated: October 13, 2016 - 2:49 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

I need to write data from a table to a csv file and save in it a directory.

I am using below code:

create or replace PROCEDURE get_query_result_as_csv_file(
in_query IN VARCHAR2,
in_filename IN VARCHAR2)
IS
l_blob BLOB;
l_file bfile
l_raw RAW(32767);
l_cursor INTEGER;
l_cursor_status INTEGER;
l_col_count NUMBER;
l_col_val VARCHAR2(32767);
l_desc_tbl sys.dbms_sql.desc_tab2;
BEGIN
-- create temporary BLOB
dbms_lob.createtemporary(l_blob, FALSE);
-- open BLOB
dbms_lob.open(l_blob, dbms_lob.lob_readwrite);
-- open cursor (and get cursor id)
l_cursor := dbms_sql.open_cursor;
-- parse query
dbms_sql.parse(l_cursor, in_query, dbms_sql.native);
-- get number of columns and description
dbms_sql.describe_columns2(l_cursor, l_col_count, l_desc_tbl);
-- define report columns
FOR i IN 1 .. l_col_count LOOP
dbms_sql.define_column(l_cursor, i, l_col_val, 32767);
END LOOP;
-- write column headings to CSV file
FOR i IN 1 .. l_col_count LOOP
l_col_val := l_desc_tbl(i).col_name;
IF i = l_col_count THEN
l_col_val := '"' || l_col_val || '"' || chr(10);
ELSE
l_col_val := '"' || l_col_val || '",';
END IF;
l_raw := utl_raw.cast_to_raw(l_col_val);
dbms_lob.writeappend(l_blob, utl_raw.length(l_raw), l_raw);
END LOOP;
-- execute the query
l_cursor_status := dbms_sql.execute(l_cursor);
-- write result set to CSV file
LOOP
EXIT WHEN dbms_sql.fetch_rows(l_cursor) <= 0 OR dbms_sql.last_row_count > 1000;
FOR i IN 1 .. l_col_count LOOP
dbms_sql.column_value(l_cursor, i, l_col_val);
IF i = l_col_count THEN
l_col_val := '"' || l_col_val || '"' || chr(10);
ELSE
l_col_val := '"' || l_col_val || '",';
END IF;
l_raw := utl_raw.cast_to_raw(l_col_val);
dbms_lob.writeappend(l_blob, utl_raw.length(l_raw), l_raw);
END LOOP;
END LOOP;
-- close cursor and BLOB
dbms_sql.close_cursor(l_cursor);
dbms_lob.close(l_blob);

END;

But with no output.
Please suggest if der is another way of doing this in a better way.
Also using above code I am not getting any file in output.
Thanks!!!

and Chris said...

Why are you using a blob?! That's for binary data. A csv is text!

And you're not actually writing the contents of the blob to a file at any point! So it's not surprise it's empty...

Anyway, Tom put together a routine to write a table to CSV using utl_file. You can find it at:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:88212348059

Rating

  (4 ratings)

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

Comments

set SQLFORMAT in SQLCI

Rajeshwaran, Jeyabal, October 13, 2016 - 10:20 am UTC

or, get into SQLCI - that would help us to get the CSV output on the client side, just spool them to a Text file.

demo@ORA11G> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.


demo@ORA11G> set sqlformat csv


demo@ORA11G> select * from scott.dept;
"DEPTNO","DNAME","LOC"
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"

4 rows selected.




Chris Saxon
October 13, 2016 - 2:48 pm UTC

I was assuming that they wanted to write the file to the DB server. But if they want it locally, yes this a great method. Also works in SQL Dev.

Milind, October 13, 2016 - 10:45 am UTC

Hi Rajeshwaran,

What is SQLCI? Is it SQL*Plus or some different tool? I did not find set csvformat option in SQL*Plus.

Regards.

Chris Saxon
October 13, 2016 - 2:49 pm UTC

See details from Rajesh below.

on SQLCI

Rajeshwaran, Jeyabal, October 13, 2016 - 11:13 am UTC

SQLCI is the new enhanced version of sql*plus provided by Oracle SQL Developer Team.

You can see more about that from JeffSmith here
http://www.thatjeffsmith.com/archive/2016/05/oracle-sqlcl-why/

to get access to SQLCI, it is available for download from SQL Developer site.

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

go to the bottom of the page, you see this "Command Line - SQLcl - Production".

or if you got latest version of SQL Developer available, go into $SQLDEV-HOME/sqlcl/bin will have "sql"

D:\>cd D:\app\sqldeveloper\sqlcl\bin

D:\app\sqldeveloper\sqlcl\bin>dir
 Volume in drive D is RAJESH
 Volume Serial Number is E079-099C

 Directory of D:\app\sqldeveloper\sqlcl\bin

09/23/2016  07:54 PM    <DIR>          .
09/23/2016  07:54 PM    <DIR>          ..
09/16/2016  12:05 PM            12,413 sql
09/16/2016  12:05 PM             2,585 sql.bat
09/16/2016  12:05 PM           398,866 sql.exe
               3 File(s)        413,864 bytes
               2 Dir(s)  330,668,666,880 bytes free

D:\app\sqldeveloper\sqlcl\bin>sql demo/demo@ora11g

SQLcl: Release 4.2.0 Production on Thu Oct 13 16:42:05 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Milind, October 14, 2016 - 6:05 am UTC

Thanks a lot for the information and prompt reply.
Regards.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here