Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, shipra .

Asked: May 02, 2000 - 1:20 pm UTC

Last updated: October 27, 2020 - 1:41 am UTC

Version:

Viewed 100K+ times! This question is

You Asked

Tom,
I need to export data from a table into a .csv file. I need to
have my column headres in between "" and data separated by ','.
also depending on the column values a row may be printed upto 5
times with data differing in only one field.
Also I need to run this program
on a daily basis.



and we said...


I'm going to suggest UTL_FILE for this task since:

- you have great control over the output file format and you seem to need that here.
- you can easily use dbms_job to schedule this procedure to run automatically every day at a certain time.


Here is a working example. I dump dates in a non-standard format (but one that saves all of the fields). you can modify as you wish.

ops$tkyte@8i> create or replace procedure dump_table_to_csv( p_tname in varchar2,
2 p_dir in varchar2,
3 p_filename in varchar2 )
4 is
5 l_output utl_file.file_type;
6 l_theCursor integer default dbms_sql.open_cursor;
7 l_columnValue varchar2(4000);
8 l_status integer;
9 l_query varchar2(1000)
10 default 'select * from ' || p_tname;
11 l_colCnt number := 0;
12 l_separator varchar2(1);
13 l_descTbl dbms_sql.desc_tab;
14 begin
15 l_output := utl_file.fopen( p_dir, p_filename, 'w' );
16 execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
17
18 dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
19 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
20
21 for i in 1 .. l_colCnt loop
22 utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );
23 dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
24 l_separator := ',';
25 end loop;
26 utl_file.new_line( l_output );
27
28 l_status := dbms_sql.execute(l_theCursor);
29
30 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
31 l_separator := '';
32 for i in 1 .. l_colCnt loop
33 dbms_sql.column_value( l_theCursor, i, l_columnValue );
34 utl_file.put( l_output, l_separator || l_columnValue );
35 l_separator := ',';
36 end loop;
37 utl_file.new_line( l_output );
38 end loop;
39 dbms_sql.close_cursor(l_theCursor);
40 utl_file.fclose( l_output );
41
42 execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
43 exception
44 when others then
45 execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
46 raise;
47 end;
48 /

Procedure created.

ops$tkyte@8i> exec dump_table_to_csv( 'emp', '/tmp', 'tkyte.emp' );

PL/SQL procedure successfully completed.

ops$tkyte@8i> host cat /tmp/tkyte.emp
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,SMITH,CLERK,7902,17-dec-1980 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,20-feb-1981 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,22-feb-1981 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,02-apr-1981 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,28-sep-1981 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,01-may-1981 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,09-jun-1981 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,09-dec-1982 00:00:00,3000,,20
7839,KING,PRESIDENT,,17-nov-1981 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,08-sep-1981 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,12-jan-1983 00:00:00,1100,,20
7900,JAMES,CLERK,7698,03-dec-1981 00:00:00,950,,30
7902,FORD,ANALYST,7566,03-dec-1981 00:00:00,3000,,20
7934,MILLER,CLERK,7782,23-jan-1982 00:00:00,1300,,10


See the Supplied Packages guide for info on utl_file. You must set some init.ora parameters to enable it to work correctly....

This can be done via sqlplus as well -- just not as nicely in my opinion.



Rating

  (114 ratings)

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

Comments

Spool Vs utl_file when # of records are 90 Million

Venkat, December 22, 2003 - 4:21 pm UTC

Hi tom,

We have a table with 90 Million Records. We need to create a CSV file and need to send this file to some external third party. Right now, we are spooling into some OS file ("," Separated) and sending this file. But to spool 90 Million records, its taking almost 4 hours.

Will using utl_file instead of spool, increase the speed? which one is better?? Please suggest.

thanks
Venkat

Tom Kyte
December 22, 2003 - 5:45 pm UTC

none of the above, you want speed -- use a little c.

here:

</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

you'll find in order of speed:

utl_file (slowest but really handy when it is "fast enough")
sqlplus
C




UTL_FILE_DIR Export to CSV

denni50, September 15, 2004 - 9:18 am UTC

Hi Tom

I have several tables that I need to export to a csv file and I'm using
your dump_table_to_csv procedure.

I am testing this on a server with oracle 9.2.0.5 and we are no longer
using the utl_file_dir initialization parameter instead we have a directory
for I/O.

The directory below is from our third party software of which I attempted
to use in executing the procedure(had to supplement actual names with xxxxx's
for proprietary reasons).

Should I create my own directory for this purpose?
There is also a client_side TEXT_IO package, would that work?


SQL> exec dump_table_to_csv( 'xxxxx', 'xxx_DIR', 'xxx.xxxxx');
BEGIN dump_table_to_csv( 'xxxxx', 'xxx_DIR', 'xxx.xxxxx'); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "XXX.DUMP_TABLE_TO_CSV", line 41
ORA-06512: at line 1


thanks

 

Tom Kyte
September 15, 2004 - 10:06 am UTC

what is line 41 of your dump_table_to_csv

Compiled Procedure

denni50, September 15, 2004 - 10:12 am UTC

it's the exception area............

 SQL> create or replace procedure dump_table_to_csv( p_tname in varchar2,
  2                                                 p_dir   in varchar2,
  3                                                 p_filename in varchar2 )
  4      is
  5         l_output        utl_file.file_type;
  6         l_theCursor     integer default dbms_sql.open_cursor;
  7         l_columnValue   varchar2(4000);
  8         l_status        integer;
  9         l_query         varchar2(1000)
 10                         default 'select * from ' || p_tname;
 11         l_colCnt        number := 0;
 12         l_separator     varchar2(1);
 13         l_descTbl       dbms_sql.desc_tab;
 14     begin
 15         l_output := utl_file.fopen( p_dir, p_filename, 'w' );
 16         execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' '
 17         dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
 18         dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 19          for i in 1 .. l_colCnt loop
 20            utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name|| '"' );
 21            dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
 22            l_separator := ',';
 23         end loop;
 24         utl_file.new_line( l_output );
 25         l_status := dbms_sql.execute(l_theCursor);
 26          while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
 27             l_separator := '';
 28             for i in 1 .. l_colCnt loop
 29                 dbms_sql.column_value( l_theCursor, i, l_columnValue );
 30                 utl_file.put( l_output, l_separator || l_columnValue );
 31                 l_separator := ',';
 32             end loop;
 33             utl_file.new_line( l_output );
 34         end loop;
 35         dbms_sql.close_cursor(l_theCursor);
 36         utl_file.fclose( l_output );
 37         execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
 38     exception
 39        when others then
 40             execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
 41             raise;
 42  end;
 43  /

Procedure created.

SQL> 

thanks 

Tom Kyte
September 15, 2004 - 10:15 am UTC

comment out the exception and see whence the error is actually coming from.

making progress.........

denni50, September 15, 2004 - 10:25 am UTC

thanks Tom...I did comment it out and re-executed and it appears to have cleared
that issue...however I don't think the data actually got exported???
The output file-name is in the correct directory path but there is no data.
Do I need to use and extension like .txt or.csv?

I'll get this figured out eventually.

thanks!



 38     exception
 39        when others then
 40             execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
 41             --raise;
 42  end;
 43  /

Procedure created.

SQL> exec dump_table_to_csv( 'xxxxx', 'xxx_DIR', 'xxx.xxxxx');

PL/SQL procedure successfully completed.

SQL>  

Tom Kyte
September 15, 2004 - 10:48 am UTC

no, comment out the EXCEPTION BLOCK

lines 38 - 41 inclusive.

Tom....one last question

denni50, September 15, 2004 - 10:40 am UTC

when I use the:
SQL> host cat /XXX_DIR1/xxx.xxxxx

to monitor the progress all I see is a momentary flash
of a blank DOS screen?

This table has about 250,000+ records, I have other tables
with 8 mil+ records.

What would be a reasonable time-frame for the export?...
and is this actually working in the background and how can
I tell?

thanks one last time...  :~)
 

Tom....

denni50, September 15, 2004 - 11:04 am UTC

I commented out the exception block and now it's not
compiling. In 9i do we need to use the utl_fflush after
each record gets written?(thought I read something about 
that somewhere).


 SQL> create or replace procedure dump_table_to_csv( p_tname in varchar2,
  2                                                 p_dir   in varchar2,
  3                                                 p_filename in varchar2 )
  4      is
  5         l_output        utl_file.file_type;
  6         l_theCursor     integer default dbms_sql.open_cursor;
  7         l_columnValue   varchar2(4000);
  8         l_status        integer;
  9         l_query         varchar2(1000)
 10                         default 'select * from ' || p_tname;
 11         l_colCnt        number := 0;
 12         l_separator     varchar2(1);
 13         l_descTbl       dbms_sql.desc_tab;
 14     begin
 15         l_output := utl_file.fopen( p_dir, p_filename, 'w' );
 16         execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
 17         dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
 18         dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 19          for i in 1 .. l_colCnt loop
 20            utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name|| '"' );
 21            dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
 22            l_separator := ',';
 23         end loop;
 24         utl_file.new_line( l_output );
 25         l_status := dbms_sql.execute(l_theCursor);
 26          while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
 27             l_separator := '';
 28             for i in 1 .. l_colCnt loop
 29                 dbms_sql.column_value( l_theCursor, i, l_columnValue );
 30                 utl_file.put( l_output, l_separator || l_columnValue );
 31                 l_separator := ',';
 32             end loop;
 33             utl_file.new_line( l_output );
 34         end loop;
 35         dbms_sql.close_cursor(l_theCursor);
 36         utl_file.fclose( l_output );
 37         execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
 38   /* exception
 39        when others then
 40             execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
 41             --raise;*/
 42  end;
 43  /

Procedure created.

SQL> exec dump_table_to_csv( 'xxxxx', 'xxx_DIR', 'xxx.xxxxx');
BEGIN dump_table_to_csv( 'xxxxx', 'xxx_DIR', 'xxx.xxxxx'); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 375
ORA-06512: at "SYS.UTL_FILE", line 696
ORA-06512: at "XXX.DUMP_TABLE_TO_CSV", line 33
ORA-06512: at line 1
 

Tom Kyte
September 15, 2004 - 11:38 am UTC

this will have nothing to do with "directories".

how big is the line you are writing out? more then 1022 ? use the 4th parameter to open and pass it say 32760

Will get it working!

denni50, September 15, 2004 - 11:24 am UTC

Tom

I uncommented out the exception block...my research indicates when
using UTL_FILE_DIR an exception block needs to be used to capture any
possible errors. In this case I just added the fclose statement.
However I am going to add the predefined exceptions that come with
the package.
I also added the .csv extension to the output file however only one
record got written to the file. I think I read somewhere that you need
to use the fflush after every record gets written.

When I come up with working code for 9i and using directories for 
file paths I'll post it here for others to use.

thanks


SQL> create or replace procedure dump_table_to_csv( p_tname in varchar2,
  2                                                 p_dir   in varchar2,
  3                                                 p_filename in varchar2 )
  4      is
  5         l_output        utl_file.file_type;
  6         l_theCursor     integer default dbms_sql.open_cursor;
  7         l_columnValue   varchar2(4000);
  8         l_status        integer;
  9         l_query         varchar2(1000)
 10                         default 'select * from ' || p_tname;
 11         l_colCnt        number := 0;
 12         l_separator     varchar2(1);
 13         l_descTbl       dbms_sql.desc_tab;
 14     begin
 15         l_output := utl_file.fopen( p_dir, p_filename, 'w' );
 16         execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
 17         dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
 18         dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 19          for i in 1 .. l_colCnt loop
 20            utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name|| '"' );
 21            dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
 22            l_separator := ',';
 23         end loop;
 24         utl_file.new_line( l_output );
 25         l_status := dbms_sql.execute(l_theCursor);
 26          while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
 27             l_separator := '';
 28             for i in 1 .. l_colCnt loop
 29                 dbms_sql.column_value( l_theCursor, i, l_columnValue );
 30                 utl_file.put( l_output, l_separator || l_columnValue );
 31                 l_separator := ',';
 32             end loop;
 33             utl_file.new_line( l_output );
 34         end loop;
 35         dbms_sql.close_cursor(l_theCursor);
 36         utl_file.fclose( l_output );
 37         execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
 38    exception
 39        when others then
 40             utl_file.fclose( l_output );
 41             --execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
 42             --raise;
 43  end;
 44  /

Procedure created.

SQL> exec dump_table_to_csv( 'xxxxx', 'xxx_DIR', 'xxx_xxxxx.csv');

PL/SQL procedure successfully completed.



 

Tom Kyte
September 15, 2004 - 11:42 am UTC

that is just HIDING THE ERROR.

the code compiled, the code above ran, the code hit a runtime error that you are now just *hiding* from view.

all lines 38..42 -- comment out -- points to line 33 being the issue, leads me to believe the line length exceeds the default of 1022, add the 4th parameter.

Thanks a MILLION Tom!!

denni50, September 15, 2004 - 12:11 pm UTC

that was it...I added 32760 to the l_output and
it completed in no time.

however I did add the exception code to the block..
just for my knowledge and easier debugging.

yippeee!

Tom Kyte
September 15, 2004 - 12:53 pm UTC

make sure to put the RAISE back into the exception block!

Tom...I did this

denni50, September 15, 2004 - 12:59 pm UTC

exception
when utl_file.invalid_path then
raise_application_error(-20100,'Invalid Path');
when utl_file.invalid_mode then
raise_application_error(-20101,'Invalid Mode');
when utl_file.invalid_operation then
raise_application_error(-20102,'Invalid Operation');
when utl_file.invalid_filehandle then
raise_application_error(-20103,'Invalid FileHandle');
when utl_file.write_error then
raise_application_error(-20104,'Write Error');
when utl_file.read_error then
raise_application_error(-20105,'Read Error');
when utl_file.internal_error then
raise_application_error(-20106,'Internal Error');
when others then
utl_file.fclose( l_output );
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
--raise;
end;

Tom Kyte
September 15, 2004 - 1:01 pm UTC

remove the -- in front of RAISE

when others then
utl_file.fclose( l_output );
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise;
end;


when others, not followed by a raise, it almost (almost) certainly a BUG.


if some error happened that you did not account for -- your when others would silently "hide" it from you!

Tom..

denni50, September 15, 2004 - 1:12 pm UTC

can you please explain that(about raise;).

none of the books,manuals or documentation that
I am researching on using the utl_file_dir package mentions that.

what does raise; do?

Tom...don't know what any of us would do if something
ever happened to you...you teach us so much stuff that
isn't written or recorded any where else.
Can we get one of those research labs to 'clone' you...
like 'Dolly' the sheep.

thanks another zillion, I'll try not to bother you anymore.






Tom Kyte
September 15, 2004 - 1:31 pm UTC

it re-raises an error.

I've written hundreds of times

"a when others that is not followed immediately by a raise is almost certainly a bug"


what would happen if the code got an Ora-1555, snapshot too old. You would flip into the when others -- clean up and return -- leaving the caller of this dump routine thinking "ah, everything worked, cool". they have NO IDEA it was an utter failure.

so, when others should

a) cleanup
b) re-raise the error via RAISE;

so the caller knows....

cloning me, scary thought.


I am still unable to get how to add 32760 to l_output

Anurag Jain, September 17, 2004 - 5:40 am UTC

Tom, could you please tell me what you meant with adding 32760 to l_output?

Tom Kyte
September 17, 2004 - 9:19 am UTC

<quote>
how big is the line you are writing out? more then 1022 ? use the 4th
parameter to open and pass it say 32760
</quote>

is what I said -- i said "make the line length for utl_file 32k instead of the default 1022 bytes". that was the "IO" error they were getting, they exceeded the default buffer size.

Anurag

reader, September 17, 2004 - 10:24 am UTC

change this line:

l_output := utl_file.fopen( p_dir, p_filename, 'w' );

to:
l_output := utl_file.fopen( p_dir, p_filename, 'w', 32760);


This was exactly what I needed to know.

Cy Ball, November 10, 2004 - 1:56 pm UTC

We needed to create a csv file from a view in our database that has 180 columns, some of which were 4000 characters wide. This is a flattened view of our data so that unsophisticated users can build their own ad-hoc reports in Excel or Access.


This was perfect.

Thanks so much.

quoting the column values

Mark, April 12, 2006 - 12:22 am UTC

this long running thread has proven very useful. I have noted in some of the examples that the original script was amended to always put double quotes around the fields.

I modified my version in a slightly different manner.
prior to the utl_file.put( l_output, l_separator || l_columnValue ) I added


-- if the separator is embedded in the value then ensure value is double quoted
-- ??? what if an embedded double quote
if instr(l_columnValue, l_separator) != 0 then
if (substr(l_columnValue, 1,1) = '"' and substr(l_columnValue, length(l_columnValue),1) = '"') then
null;
else
l_columnValue := '"' || l_columnValue || '"';
end if;
end if;

As noted above I would have an issue with a string which may include double quotes as part of the column value.

I could replace/strip any existing double quotes but that changes the data which I don't want to do.

Perhaps I can escape the existing double quotes so they don't cause a problem.

Is there a method you would suggest that would overcome this problem.

Tom Kyte
April 12, 2006 - 8:01 am UTC

You have to tell us - the thing that will "read" this result file - what would IT like to see as far as escaping goes.

It really depends on what is going to read and process this file later, that'll dictate how YOU escape your data.

ORA-29285

Irene Westervelt, May 11, 2006 - 3:18 pm UTC

Thx a lot - I changed my output line to 32KB too and it worked after I almost gave up

Ramesh, October 20, 2006 - 9:29 pm UTC

Hi Tom,

I want the csv output to be dumped in my machine where it has only oracle client. Is it possible with this proc ? If so, does the init.ora setup changes ?

Thanks
Ramesh

Tom Kyte
October 21, 2006 - 11:03 am UTC

sqlplus

</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

utl-file can ONLY write to files on the server, not to your local PC (that would be very virus like if it could)

Oracle to CSV export

Ken Haagner, November 07, 2006 - 7:18 am UTC

Excellent - most useful for views

Full CSV column quoting support

Jeff, December 28, 2006 - 1:12 pm UTC

Back in April, Mark from Australia posted some code changes that would handle cases where a comma was embedded in a column value, but not double-quotes. The CSV spec (such as there is) requires embedded double-quotes to be changed to two double-quotes and the whole value enclosed inside a pair of double-quotes. Here is the code needed to handle this and the embedded comma problem in one go. It also solves the problem Mark would have in that his code will quote the first value of every row. This replaces your entire while loop since there was some significant change to most lines.
    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            -- if the separator or quote is embedded in the value then enclose in double-quotes
            if instr(l_columnValue, ',') != 0 or instr(l_columnvalue, '"') then
                l_quote := '"';
                -- double any/all embedded quotes
                l_columnvalue := replace(l_columnvalue,'"','""');
            else
                l_quote := '';
            end if;
            utl_file.put( l_output, l_separator || l_quote || l_columnValue || l_quote);
            l_separator := ',';
        end loop;
        utl_file.new_line( l_output );
    end loop;


This has been a very useful thread and saved me about a half-day of coding/testing. Thanks.

Full CSV column quoting support

Jeff, December 28, 2006 - 1:17 pm UTC

Oops, had a typo on l_columnValue in a couple of lines. This is the corrected code.
    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            -- if the separator or quote is embedded in the value then enclose in double-quotes
            if instr(l_columnValue, ',') != 0 or instr(l_columnValue, '"') then
                l_quote := '"';
                -- double any/all embedded quotes
                l_columnValue := replace(l_columnValue,'"','""');
            else
                l_quote := '';
            end if;
            utl_file.put( l_output, l_separator || l_quote || l_columnValue || l_quote);
            l_separator := ',';
        end loop;
        utl_file.new_line( l_output );
    end loop;


export to csv

Ash, February 26, 2007 - 1:22 am UTC

Can the exportation from SQL to CSV be done using DBMS_Output instead of UTL.FILE?
Tom Kyte
February 26, 2007 - 2:37 pm UTC

if you are going to use dbms_output, just use SQL

http://asktom.oracle.com/tkyte/flat/index.html

no plsql needed - simple spool from sqlplus.

End of line character

Dave, February 27, 2007 - 8:43 pm UTC

Hi Tom,

is it possible to set the end of line character for UTL_FILE?
I see the documentation says it uses the OS' character (our DB is on Linux).
Just wondering if i could pass in either the dos or unix end of line character (in hex or whatever) and have UTL_FILE write out either. right now we have another process that grabs the file and converts to dos where needed but id like to just take care of it in one step if possible for the files that need to be in dos.
eg use UTL_FILE.PUT(my_string || my_eol_char)... where my_eol_char is '0D0A' or '0A'
obviously this wouldnt work but hopefully it gives you an idea what i mean.

does this make sense?
Tom Kyte
February 28, 2007 - 2:57 pm UTC

no, utl_file is platform agnostic and puts out the end of line the platform it is running on expects to have output.

now, since DOS is "\r\n" (0x0D 0x0A) and others are "\n", maybe all you need to do is (since you are on linux) is add your own 0x0D to the end of line - we'll add the 0x0A. Yes, you would have to add that to each line.

SQLPLUS query output to csv format

Andy, March 01, 2007 - 4:35 am UTC

Tom, is there anyway to do a query in sqlplus, then have the result output to a file in csv format without invoking UTL_FILE, using only sqlplus command.

Example
SELECT A, B, C, D, FROM TABLE WHERE A=x;
The resulting output will be in csv format as below
A,B,C,D
x,<value>,<value>,<value>
x,<value>,<value>,<value>
x,<value>,<value>,<value>
(assuming there are only 3 result)
and the .csv file MUST be save as x.csv

can it be done?
Tom Kyte
March 02, 2007 - 11:12 am UTC

SQLPLUS query output to csv format

Andy, March 04, 2007 - 9:03 pm UTC

I have tried using flat.sql, however i have received error messages as below

SQL> @ytmpy.sql
SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "SQL> promp..." - rest of line ignored.

what can i do to rectify it?
Tom Kyte
March 05, 2007 - 12:30 pm UTC

debug it - it is pretty simple stuff there - look at it, understand it, rectify it.

you probably have settings in your login.sql that are making it not work - such as set echo on or something.

Tom Fox, March 05, 2007 - 12:47 pm UTC

SP2-0042: unknown command "SQL>" - rest of line ignored. 


Looks like you may have your SQL prompt inside the .sql file.

A reader, March 06, 2007 - 4:40 am UTC


Filename

Andy, March 12, 2007 - 1:16 am UTC

Sorry.. i still have problem in trying to save a specify filename...
Tom Kyte
March 12, 2007 - 8:24 pm UTC

"sorry"???? no idea what you mean.

I am getting error

Dibyendu, August 23, 2007 - 6:28 am UTC

Hi Tom,
I am using almost same procedure what you have given earlier. The procedure is as follows:
create or replace procedure dump_table_to_csv( p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2 )
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000)
default 'select * from ' || p_tname;
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
dbms_output.put_line(0);
l_output := utl_file.fopen( p_dir, p_filename, 'w',32760 );
dbms_output.put_line(1);
execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''
';
dbms_output.put_line(2);
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
dbms_output.put_line(3);
for i in 1 .. l_colCnt loop
utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"'
);
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := ',';
end loop;
dbms_output.put_line(4);
utl_file.new_line( l_output );
l_status := dbms_sql.execute(l_theCursor);
dbms_output.put_line(5);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
end loop;
dbms_output.put_line(6);
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
dbms_output.put_line(7);
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise_application_error(-20002, 'Some unknown error');
end;
/

But when I run the procedure using
15:46:51 SCOTT: ORACTL> exec dump_table_to_csv( 'emp', '/tmp', 'tkyte.emp' );

I am getting the following output:

1
BEGIN dump_table_to_csv( 'emp', '/tmp', 'tkyte.emp' ); END;

*
ERROR at line 1:
ORA-20002: Some unknown error
ORA-06512: at "SCOTT.DUMP_TABLE_TO_CSV", line 51
ORA-06512: at line 1

Could you please tell me why am I getting the error.
Tom Kyte
August 23, 2007 - 12:13 pm UTC

please debug it then and fix it.


you utterly broke it with "some unknown error" - how USEFUL IS THAT

put my raise back in and SEE WHAT ERROR YOU ARE GETTING.


csv and leading zeroes!

Jay, December 10, 2007 - 2:52 pm UTC

Hi Tom,

I have a field that has some leading zeroes. No matter what I try in my sql, the leading zeroes go away when exported in CSV format? Have you come across anything like this? Is there a solution?

I tried this.. to_char(field name,'fm00000') and it still outputs without the leading zeroes! Plain excel output is fine though.

Thanks for your help!
Jay
Tom Kyte
December 10, 2007 - 4:18 pm UTC

put them in quotes, excel is treating them as a number field, not as a string.

so, make them a string, because they are not numbers apparently...

csv in excel 2003

Crystal, September 01, 2008 - 8:58 am UTC

Hi Tom,

Your code is very helpful!

Just a question, since lower versions of excel have row limits of around 65536 rows, how can this code be tweaked to handle a query that fetches more than the limit of excel to write to another csv file continuing the data from the first file created containing the first 65K+ rows?

Thanks a lot hope to hear from you...
Tom Kyte
September 01, 2008 - 1:17 pm UTC

well, you have the code, add a counter and when it hits a limit - you would close the output file and open a new one... Pretty straight forward.

Export data from a table to .csv file

Manoj, May 21, 2009 - 1:25 am UTC

Hi Tom,

Your code is really very much helpful and it solved my big problem.

Thanks for helping everyone in need.


Text file not being written to

Thomas, July 05, 2009 - 7:11 am UTC

Hi Tom,

I was able to execute your procedure.
My directory is located at c:\extracts
This directory has file named test.txt

I executed the procedure as
exec exec dump_table_to_cs( 'EMP', 'c:\extracts', 'test.txt' );

But nothing is being written to my file.

I created the folder extracts like a normal Windows folder and i have given all permissions to it.

Am i doing something wrong? Please help.

Tom Kyte
July 06, 2009 - 8:14 pm UTC

this directory you are looking at...

it is on the DATABASE MACHINE right? You are not looking on YOUR MACHINE, you are in fact looking on the DATABASE MACHINE right??

CTAS external table

James Su, July 07, 2009 - 10:30 am UTC

Hi Tom,
CTAS an external table in datapump format is now available, why don't you support creating an external table in CSV format? Wouldn't that be a nice feature? Thanks.
Tom Kyte
July 07, 2009 - 6:40 pm UTC

it could be, you'd want to file an enhancement request (done via an SR, service request, on metalink)

Excellent info

JR, July 22, 2009 - 10:04 pm UTC

This code will save me hours of time recreating a csv export proc for each time I get this request.

I was digging around in the Oracle stored packages expecting that at least 10g would have a package that does something similar to your code here. Is there an open enhancement request or does Oracle 11 address this. Its a pretty darn common activity, exporting a query to .csv.

Thanks!
Tom Kyte
July 26, 2009 - 6:49 am UTC

sqldeveloper does it
apex does it
this does it

probably - that'll be it.

Wait a minute....

JR, July 22, 2009 - 10:10 pm UTC

I just looked over the code again, this exports a table, not a query, correct?

If i were to modify by removing the table name parameter, and simply drop a multi-join query into the l_query param, with the columns describe properly? - dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

thanks


ORA-22921

Julio, September 09, 2009 - 3:18 pm UTC

This PL/Sql does not working for Clob's datatype due I'm getting the following error ORA-22921. Any help on how should I resolve this issue?


Any help on this would be appreciated. Thanks in advanced.


JC
Tom Kyte
September 14, 2009 - 10:15 am UTC

you would have to rewrite the code to support lobs - it is rather straightforward.

 19      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );


that code fills l_descTbl, l_descTbl has the datatypes, you would modify this code:

 21      for i in 1 .. l_colCnt loop
 22          utl_file.put( l_output, l_separator || '"' || 
l_descTbl(i).col_name || '"' );
 23          dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
 24          l_separator := ',';
 25      end loop;


to "if" on the descTbl type and if it were a clob, you would bind differently (to a clob - lob locator)

and in the loop that dumps the data out - do the "if" again, and if it is a clob - use new code you write to dump the clob out, if not, use my existing code



and if you do all of this, please feel free to SHARE back with us.

Bob, October 07, 2009 - 7:36 pm UTC

The link to your "flat" procedure is no longer working. Do you have an updated location for it?

How to append the sysdate to the filename of tehe csv exported?

Fermin, October 09, 2009 - 4:03 am UTC

Hi everyone,
i am newbie in procedures but I've tried the dump_table_to_csv and it works properly.
Te only thing i would like to add it is to append the sysdate to the filename of the exported csv [I want to plan this procedure every night and have a different name of file each]. Any Help??
Thank u very much in advance!!

Quick and Dirty metthod

SR, October 19, 2009 - 9:19 am UTC

Often times you want to do a quick and dirty dump without the hassle of UTL_FILE, especially when you're just developing and wanting to send/email data out quickly as a one off, or the DBA's cant be bothered to create you a directory on the server for UTL_FILE.

In that case, provided the data isn't too much the following may be useful as well:

SET HEAD OFF
SET PAGES 0
SET LINES 5000
SET TRIMSPOOL ON
SET TAB OFF
SET FEEDBACK OFF
SET LONG 4000
COL c FORM A5000
SPOOL sr.dat
SELECT '"' || TRIM(STATID) || '","' ||
TYPE || '","' ||
VERSION || '","' ||
FLAGS || '","' ||
TRIM(C1) || '","' ||
TRIM(C2) || '","' ||
TRIM(C3) || '","' ||
TRIM(C4) || '","' ||
TRIM(C5) || '","' ||
N1 || '","' ||
N2 || '","' ||
N3 || '","' ||
N4 || '","' ||
N5 || '","' ||
N6 || '","' ||
N7 || '","' ||
N8 || '","' ||
N9 || '","' ||
N10 || '","' ||
N11 || '","' ||
N12 || '","' ||
TO_CHAR (D1, 'DD-MON-YYYY HH24:MI:SS' ) || '","' ||
RAWTOHEX(R1) || '","' ||
RAWTOHEX(R2) || '","' ||
TRIM(CH1) || '","' ||
CL1 || '"'c
FROM db_stats
/

Works well - even with CL1 being a CLOB.
If you find you have double quotes in the data then you might use REPLACE ( col, '"', '\"' ), but I haven't had that problem yet.

Dynamic SQL exporting to CSV

Karl Reitschuster, January 20, 2010 - 12:58 am UTC

It's exactly what i needed - thanks!
/Karl

invalid directory path

bahman, February 24, 2010 - 6:45 am UTC

Hi Tom,
when i ran the procedure i have error:
FEJL i linie 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 29
ORA-06512: at "SYS.UTL_FILE", line 448
ORA-06512: at "PFU6000.RATE", line 16
ORA-06512: at line 1
Tom Kyte
March 01, 2010 - 8:52 am UTC

and do you think you might be able to figure it out? It is rather clear isn't it?


it is quite possible that your directory structure and mine could be different - no?

You would need to give a full example, and remember - the directory you reference MUST be on the *server*, not on the client machine.

Vignesh, April 22, 2010 - 2:30 am UTC

UTL_FILE is useful when data is less.. what about transfering 1 million records to flat file once in a week.. will UTL_FILE be useful and fast ?
Tom Kyte
April 22, 2010 - 8:12 am UTC

my answer will always be "go ahead and benchmark it"

It might be faster than fast enough for you.

On the other hand, you might find it to be far too slow.

The answer is "it depends", you tell me if it is fast enough. Many times what person X thinks is too slow is faster than fast enough for Y and vice versa.


It depends on the person. I think $200 is too much to spend on a purse, but $200 x X (where X >= 1) on a piece of electronics that will last probably not as long as a purse would be money well spent. I'm sure there are people in the world that would disagree and say the converse was true :)

Will utl_file be useful? Yes, utl_file can be used to unload a million records.

Will utl_file be fast? Probably not as fast as SQLPlus or java or C. Will it be fast enough? Only you can say....

Will utl_file be easy and productive to do this codewise - maintaining, running, etc? Yes, definitely - fewer moving pieces, the code is in the database - no deployment issues, just schedule it and let it run.

Location of "flat".

Shannon Severance, June 02, 2010 - 12:41 pm UTC

For those looking for the above referenced http://asktom.oracle.com/~tkyte/flat/index.html, which no longer works; Google found me the new location: http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteflat.html

A reader, August 13, 2010 - 3:55 am UTC

Hi , Tom
Why
" http://asktom.oracle.com/~tkyte/flat/index.html"
Not Found
Sorry!The page requested was not found.

Invalid Operation

Berk, September 01, 2010 - 4:26 am UTC

Hi, i'm new in sql and find this topic very useful but i get this error: 12:25:19 ORA-20102: Invalid Operation
What is the solution? Thanks..
Tom Kyte
September 09, 2010 - 11:56 am UTC

my car won't start...

why not?


now - we are even, we both have given each other the same amount of information regarding our respective problems.


You have no idea what my situation is, and I have no clue what yours is.


how about.... and example that causes your error????

A full example, that I can run and reproduce your findings...

dump_table_to_csv

Latha, October 08, 2010 - 12:57 pm UTC

I ran exactly what u gave :
Below is the procedure I ran.

create or replace procedure dump_table_to_csv( p_tname in varchar2,
                                               p_dir   in varchar2,
                                               p_filename in varchar2 )
    is
       l_output        utl_file.file_type;
       l_theCursor     integer default dbms_sql.open_cursor;
       l_columnValue   varchar2(4000);
       l_status        integer;
       l_query         varchar2(1000)
                       default 'select * from ' || p_tname;
       l_colCnt        number := 0;
       l_separator     varchar2(1);
       l_descTbl       dbms_sql.desc_tab;
   begin
       l_output := utl_file.fopen( p_dir, p_filename, 'w' );
       execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
       dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
       dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
        for i in 1 .. l_colCnt loop
          utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name|| '"' );
          dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
          l_separator := ',';
       end loop;
       utl_file.new_line( l_output );
       l_status := dbms_sql.execute(l_theCursor);
        while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i, l_columnValue );
               utl_file.put( l_output, l_separator || l_columnValue );
               l_separator := ',';
           end loop;
           utl_file.new_line( l_output );
       end loop;
       dbms_sql.close_cursor(l_theCursor);
       utl_file.fclose( l_output );
       execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
 /* exception
      when others then
           execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
           --raise;*/
end;
/


I am getting the below error:

SQL> exec dump_table_to_csv( 'emp', 'ContingencyTest', 'test.csv' );
BEGIN dump_table_to_csv( 'emp', 'ContingencyTest', 'test.csv' ); END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "B2USDB.DUMP_TABLE_TO_CSV", line 15
ORA-06512: at line 1


I would want the output file to be in my current directory. Could I know how to do it?

Tom Kyte
October 11, 2010 - 11:58 am UTC

"U" isn't available, "U" is dead as far as I know. Look it up, it is true.

http://en.wikipedia.org/wiki/U_of_Goryeo




Files produced by a stored procedure that runs "in the server" - can only be produced ON the server itself. You would need a client application running on your machine to produce a file in your 'current directory' (it would be pretty virus like IF the database server could actually write to your file system wouldn't it...)


ContingencyTest would have to exist on the server.
It would have to exist under the servers current working directory (good luck with that - you cannot control what that is).

You would use a fully qualified path name always - so you actually have control.
You would have to realize that path name will be referring to the SERVER.

If you have sqlplus on your client, you can use a sqlplus script to write a file on YOUR machine instead of the server. SQLPlus - since it runs on your machine - can read and write your file system.

see
http://asktom.oracle.com/Misc/httpasktomoraclecomtkyteflat.html

for more info

Some questions about quotes in your query

Alex, November 18, 2010 - 4:55 am UTC

Hi Tom,

your procedure is very useful. Thank you. In addition i want to say 'thank you' to those who worked on this procedure (the doublequote fix is great).

Anyway this procedure has one problem left. It treatens every record as a string (it puts everything under quote). Not very useful when i have to export tables where i have multiple datatypes (e.g. numbers, dates, varchar2).
Is there a function who lets me quote only strings?
I looked a bit in the Oracle 9i manual, but i couldn't find any function who gives me back the datatype of a column so that i can use it in a IF THEN ELSE.

As a workaroung i wrote a package that quotes me only values where he finds a space in it. But this solution is stupid and does not work very well.

Actually i am thinking of another solution. But it isn't a good solution either. Should i try to write a package where you have to include the colums you want in quote in the parameters? Not sure i want to do this.

There must be a simplier solution. Can you suggest me anything?

Thank you.

Kind regards,
Alex
Tom Kyte
November 19, 2010 - 5:04 am UTC

just query the dictionary - user_tables, all_tables, whatever - the datatypes are there easily enough

Table_to_CSV

Mikko, November 18, 2010 - 6:56 am UTC

Hi Tom,
Your example is very usefull, but I have some difficulties to run the procedure with PL/SQL developer.
Procedure does run, but gives me an error message.
Procedure does compile ok.

Can you please help me.

create or replace procedure Table_to_CSV
( p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2 )
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000)
default 'select * from ' || p_tname;
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' ,'32760');
execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name|| '"' );
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
when utl_file.invalid_path then
raise_application_error(-20100,'Invalid Path');
when utl_file.invalid_mode then
raise_application_error(-20101,'Invalid Mode');
when utl_file.invalid_operation then
raise_application_error(-20102,'Invalid Operation');
when utl_file.invalid_filehandle then
raise_application_error(-20103,'Invalid FileHandle');
when utl_file.write_error then
raise_application_error(-20104,'Write Error');
when utl_file.read_error then
raise_application_error(-20105,'Read Error');
when utl_file.internal_error then
raise_application_error(-20106,'Internal Error');
when others then
utl_file.fclose( l_output );
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise;

end Table_to_CSV;

Error message:

ORA-20100:Invalid Path
ORA-06512: "xxxx.Table_to_CSV", line 41
ORA-06512: line 3





Tom Kyte
November 19, 2010 - 7:31 am UTC

looks like......

well....

an invalid path?

declare
    l_output        utl_file.file_type;
    p_dir   in varchar2 := 'something';
    p_filename in varchar2 := 'something';
begin
    l_output := utl_file.fopen( p_dir, p_filename, 'w' ,'32760');
    utl_file.fclose( l_output );
end;



if you do that (supply something for 'something'!) - what happens - fix that.

How to format numbers and dates in your CSV export

Alex, November 18, 2010 - 10:00 am UTC

Hi there,

because you helped me so much i will now share my solution on how to export a CSV with quoted texts and not quoted numbers / dates.

First create two new functions:

create or replace
FUNCTION IsDate(p_num VARCHAR2) RETURN NUMBER
AS
a DATE;
BEGIN
a := p_num;
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;


create or replace
FUNCTION Isnumber(p_num VARCHAR2) RETURN NUMBER
AS
a NUMBER;
BEGIN
a := p_num;
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;


Then you can start writing your procedure:

create or replace
procedure dump_table_to_csv( p_tname in varchar2,
                                                p_dir   in varchar2,
                                               p_filename in varchar2 )
is
     l_output        utl_file.file_type;
     l_theCursor     integer default dbms_sql.open_cursor;
     l_columnValue   varchar2(4000);
     l_status        integer;
     l_query         varchar2(1000)
                     default 'select * from ' || p_tname;
     l_colCnt        number := 0;
     l_separator     varchar2(1);
     L_DESCTBL       DBMS_SQL.DESC_TAB;
     L_QUOTE         VARCHAR2(1);
     
  begin
      L_OUTPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'w', 32760);
      execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ';
  
      dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
      DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );
   
       for i in 1 .. l_colCnt loop
         utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );
           DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );
           l_separator := ';';
       end loop;
      utl_file.new_line( l_output );
  
      l_status := dbms_sql.execute(l_theCursor);
  
      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        l_separator := '';
        for i in 1 .. l_colCnt loop
            DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );
            -- if the separator or quote is embedded in the value then enclose in double-quotes
            if ISNUMBER(l_columnValue) =0 AND ISDATE(l_columnValue) =0 then
                l_quote := '"';
                -- double any/all embedded quotes
                l_columnValue := replace(l_columnValue,'"','""');
            ELSE
                L_QUOTE := '';
            END IF;
            UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || L_QUOTE || L_COLUMNVALUE || L_QUOTE);
            l_separator := ';';
        end loop;
        utl_file.new_line( l_output );
    end loop;

       dbms_sql.close_cursor(l_theCursor);
       utl_file.fclose( l_output );
   
      execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
   exception
       when others then
           execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
           raise;
   end;


Your CSV should now be formatted as if you used the export to csv from SQL Developer.

@Followup from my first post:
Thank you, i forgot about the ALL_TABLES and ALL_COLUMS ecc.
But as you can see i didn't used them, because i found another solution.

Sorry for the bad format, but i use SQL Developer which thinks he has to uppercase everything i'm pointing on.
Tom Kyte
November 19, 2010 - 7:35 am UTC

it would be WAY more efficient to use the dictionary - maybe orders of magnitude more efficient

How about if column is Nested table

Mikko, December 07, 2010 - 7:30 am UTC

Great job guys.
everything woks ok as long as table columns are number, date or varchar.. etc.

Now I have table that have's various special colums for example long or nested table. How would you recoment deal this problem.

I'm using PL/SQL Developer in Oracle 10g

create or replace package body Table_to_csv_new is

FUNCTION IsDate(p_num VARCHAR2) RETURN NUMBER
AS
a DATE;
BEGIN
a := p_num;
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;

FUNCTION Isnumber(p_num VARCHAR2) RETURN NUMBER
AS
a NUMBER;
BEGIN
a := p_num;
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;

FUNCTION IsCLOB(p_num VARCHAR2) RETURN NUMBER
AS
a CLOB;
BEGIN
a := p_num;
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;

procedure get_tables

is

P_TNAME varchar2(2000);
P_FILENAME varchar2(2000);
RETURN_ varchar2(2);
K_ number := null;

cursor get_table is
select owner,object_name,owner||'.'||object_name as T, object_name||'.'||'CSV' as D
from all_objects
where object_type='TABLE';

cursor get_table_num is
select count(distinct object_name) as K from all_objects
where object_type='TABLE';

begin
for get_table_num_ in get_table_num loop
K_ := get_table_num_.K;
P_TNAME := null;
P_FILENAME := null;

if K_>0 then

for get_table_ in get_table loop
P_TNAME := get_table_.T;
P_FILENAME := get_table_.D;

Table_to_csv_new.dump_table_to_csv(P_TNAME, P_FILENAME, RETURN_);


end loop;
end if;
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
ROLLBACK;

end ;

procedure dump_table_to_csv ( p_tname in varchar2,
-- p_dir in varchar2,
p_filename in varchar2,
RETURN_ in out varchar2)
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
p_dir varchar2(2000);
l_query varchar2(1000)
default 'select * from ' || P_TNAME;
l_colCnt number := 0;
l_separator varchar2(1);
L_DESCTBL DBMS_SQL.DESC_TAB;
L_QUOTE VARCHAR2(1);

begin
p_dir :='c:\oracle';

L_OUTPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME, 'w', 32760);
execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ';

dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );

for i in 1 .. l_colCnt loop
utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );
DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );
l_separator := ';';
end loop;
utl_file.new_line( l_output );

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );
-- if the separator or quote is embedded in the value then enclose in double-quotes
if ISNUMBER(l_columnValue) =0 AND ISDATE(l_columnValue) =0 then
l_quote := '"';
-- double any/all embedded quotes
l_columnValue := replace(l_columnValue,'"','""');
elsif IsCLOB(l_columnValue)>0 then
L_QUOTE := '"';
l_columnValue := rtrim(rtrim(dbms_lob.substr(replace(L_COLUMNVALUE,'"','""'))));
ELSE
L_QUOTE := '';
END IF;

UTL_FILE.PUT( L_OUTPUT, L_SEPARATOR || L_QUOTE || L_COLUMNVALUE || L_QUOTE);
l_separator := ';';
end loop;
utl_file.new_line( l_output );
end loop;

dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );

execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise;
end;
end Table_to_csv_new;





Tom Kyte
December 07, 2010 - 11:10 am UTC

... table. How would you recoment deal this problem.
...

I'd recommend you come up with a file format to support it (nested table output) first

then describe that format to us

and then we can suggest how you might deal with it.



for the longs, you can piecewise read a long - you'll just need to write a bit more code to deal with it. You'll use describe_columns2 to figure out what columns are long/clobs and bind them differently - and then piecewise fetch the longs/lobs.

veera, December 09, 2010 - 12:33 am UTC

Hi Tom,

I faced on problem with files how i can achive this,This is relate of migration issue i.e

i have all scripts related source and target in directories and all scripts contain select statements,my question in when i am run one script all of the source and target script must be executed and the results are stored in the result table


How i can achive this


Tom Kyte
December 09, 2010 - 10:29 am UTC

what are the "results"???

not really sure what you mean by this.

Migration Using UTL_Files

Veera, December 09, 2010 - 5:17 am UTC

Hi Tom,

I am working on one of the migration project and over all process run thruogh
UTL_Files,but recently i face one problem

i.e

Unix directory contains source,target directorys and each directory have morethen 40 select
Statements scripts;

Hear requirment is

Write a program to read all these scripts(source,target) and display the data on the screen.


When i executes the program automatically all of the source and target scripts must by
run and display the result set on the screen.



Please give me briff explanation and small example of this one




Tom Kyte
December 09, 2010 - 10:59 am UTC

you would have to write sqlplus in order to do that - you would have to reinvent sqlplus in plsql.

That is a brief explanation of what you would have to do. As for a small sample, not going to happen.


I think you might need to change your requirement - the requirement should be:

write one more script that issues

o set echo on
o spool log.txt

and maybe

o set errorlog on
http://asktom.oracle.com/Misc/new-thing-about-sqlplus.html

and then runs the remaining scripts in their proper order using sqlplus.



dump csv

sam, March 07, 2011 - 11:03 pm UTC

TOM:

I need to dump a query (view) into CSV format with column headings into a user browser so when he clicks on link, the dump_csv runs and prompts the user to download file into his client machine using browser/Mod_plsql. User will import this file in Excel.

WOuld the dump_csv function still be useful here or you would simply run the cursor and HTp.p(result_set) with a text file heading so that user can save.

Have you written a routine for this or how would you modify the above?

thanks
Tom Kyte
March 08, 2011 - 12:40 pm UTC

if you want, we did that using the SYLK standard format file - excel deals with that nicely

http://asktom.oracle.com/pls/asktom/asktom.search?p_string=owa_sylk

SYLK

Sam, March 08, 2011 - 7:46 pm UTC

Tom:

Would this SYLK format server a similar function as CSV?

When they download the file does it automatically open Excel and place the values there in cells so they only need to save the excel file?
Tom Kyte
March 09, 2011 - 7:37 am UTC

Sam - research it.

Do you know how a browser automagically "knows what to open"? That is done via a mime type.

Do you know if excel knows how to deal with SYLK - you could read around and find out, you could just read the web page we put together.

the entire idea behind owa_sylk was only to create a spreadsheet for excel. The answer should be somewhat obvious from the pages.

Oracle dump command setting null dates to 1/1/1753

Caesar, April 07, 2011 - 3:42 pm UTC

Hi Tom,
Hopefully this is deemed a true follow-up and not 'new'. I've read and implemented the code noted in the thread with great success. Reduction in output time went from 45 min using Toad to 14 seconds!! That's some ROI! But I've encountered a new twist. I am seeing 1/1/1753 00:00:00 and 12/30/1899 00:00:00 as default dates in my csv output. I've scanned the net to no avail. Is there a default capacity to set null dates to a charset or some other override? Or shall I handle these known default dates in my ETL tool instead? I was hoping the export function would have an override.
Tom Kyte
April 12, 2011 - 1:34 pm UTC

Those dates mean nothing to me - I'm not sure what you are doing, or where/how you are encountering them??

We don't have the concept of "default dates". You'll need to be a bit more descriptive about what is going on - what you've done and how you are getting the data.

Are you using a spreadsheet?

AndyP, April 13, 2011 - 8:38 am UTC

To Caesar from Boston, just checking if you are looking at your results in a spreadsheet, having generated them as csv. I had this catch me out when excel was trying to be clever and interpreted some data I had with slashes in it to be a date. That put 1st of the 1st of some unlikely-looking year in there, which is how I spotted it. If this is the case, if you declare the column to be Text rather than General when importing into Excel, that prevents it from doing it. I also find that exporting as tab-delimited rather than csv is safer as regards transition to spreadsheet

Leading Zero

Selva, May 19, 2011 - 5:28 am UTC

Hi Tom,
It is very useful for me. The Coding is running successful. The output in csv file the leading zeros all missing. Can u say the solution this problem.

Thanks in Advance.
Take care
Tom Kyte
May 19, 2011 - 8:43 am UTC

numbers do not have leading zeros.

a string can have zeros, but a number - no way, there is no such thing.


you would have to select TO_CHAR( number_column, '000000009' ) .... to get a string with zeros on the leading edge.


and even then, when you open the csv in excel and it sees a number - it will turn it into a number and bam - there goes your leading zeros again.


so, in short, if the data in your table is a number - there are no leading zeros, you'll have to convert it into a string (use a view)

if the data in your table is a string and has leading zeros, the problem lies in excel, it is turning it into a number (and hence it'll have no leading zeros since numbers don't have that concept)

support for XE

hitgon, June 23, 2011 - 3:28 am UTC

We have the Oracle XE Database.

Following proc will work fine in EE but in XE will create some compilation errors.
 

create or replace procedure dump_table_to_csv( p_tname in varchar2,
                                                p_dir   in varchar2,
                                                p_filename in varchar2 )
 is
     l_output       utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_query         varchar2(1000)
                     default 'select * from ' || p_tname;
     l_colCnt        number := 0;
     l_separator     varchar2(1);
     l_descTbl       dbms_sql.desc_tab;
  begin
  l_output := utl_file.fopen( p_dir, p_filename, 'w' );
   execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''';
     dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
     dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 
     for i in 1 .. l_colCnt loop
     utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' 
);
          dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
         l_separator := ',';
       end loop;
       utl_file.new_line( l_output );
   
       l_status := dbms_sql.execute(l_theCursor);
   
       while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
          l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i, l_columnValue );
               utl_file.put( l_output, l_separator || l_columnValue );
               l_separator := ',';
           end loop;
           utl_file.new_line( l_output );
       end loop;
       dbms_sql.close_cursor(l_theCursor);
       utl_file.fclose( l_output );
   
       execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
   exception
       when others then
          execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
          raise;
   end;
   /
-----------------------------------------------
show error
SQL> show error
Errors for PROCEDURE DUMP_TABLE_TO_CSV:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/21     PL/SQL: Item ignored
5/21     PLS-00201: identifier 'UTL_FILE' must be declared
15/3     PL/SQL: Statement ignored
15/3     PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

21/6     PL/SQL: Statement ignored
21/20    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

26/8     PL/SQL: Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
26/27    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

34/16    PL/SQL: Statement ignored
34/30    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

37/12    PL/SQL: Statement ignored
37/31    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed


LINE/COL ERROR
-------- -----------------------------------------------------------------
40/8     PL/SQL: Statement ignored
40/25    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed


Tom Kyte
June 23, 2011 - 8:08 am UTC

just grant execute on utl_file to the owner of this procedure.

support for XE

hitgon, June 23, 2011 - 5:03 am UTC

grant execute on UTL_FILE to public;

after execute the above command
procedure will working successfully.

Regards
hitgon

Problem in exporting table into a csv file

prasanna, July 01, 2011 - 11:53 am UTC

Hi Tom,
Your procedure regarding exporting the table into cs file is more useful for me and i'm facing an issue while running the procedure. The procedure created successfully but while executing i'm facing the following issue.
Could you please help me on this. Sorry if the question is a repetitive one but i couldn't able to solve it.

create or replace procedure dump_table_to_csv( p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2 )
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000)
default 'select * from ' || p_tname;
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''
';

dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"'
);
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := ',';
end loop;
utl_file.new_line( l_output );

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );

execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise;
end;
/


Tom Kyte
July 01, 2011 - 12:01 pm UTC

I don't see any issues??? I just see my code.

Problem in exporting table into csv file

prasanna, July 01, 2011 - 8:35 pm UTC

Hi Tom,
Sorry to mention my error which i got while executing the procedure,
ORA-29280: invalid directory path
ORA-06512: at "xxxx.DUMP_TABLE_TO_CSV", line 48
ORA-06512: at line 2
Tom Kyte
July 05, 2011 - 7:37 am UTC

ctl-f and search for that error code on this page.

That is relatively self explanatory - you have not set up a directory yet, if you just test with a simple, standalone utl_file.fopen call of your own - you will discover you cannot open a file in the directory you are trying. You'll need to get that setup first.

hi tom

raunka, August 03, 2011 - 11:30 pm UTC

hi tom i want to ask you that the code for exporting a sql file to a csv file is working and it is mentioned that procedure is created but the output is not displaying so what to do for obtaining the output.please reply as soon as possible.
Tom Kyte
August 04, 2011 - 10:18 am UTC

what do you mean "output is not displaying"

this routine creates a file, that file resides on the database server. simply edit that file.

dump_table_to_csv procedure

shiva, August 19, 2011 - 6:32 am UTC

I am getting the error like this
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "COLLREPORT.CSEXPO_TABLE_TO_CSV", line 38
ORA-06512: at line 1

what is meant by directory path.i am thinking that where the csv file will be created that path we need to give in the procedure please help me in this error
Tom Kyte
August 30, 2011 - 1:05 pm UTC

You have to

create or replace directory YOUR_DIRECTORY_NAME_WHATEVER_YOU_WANT_TO_CALL_IT
as '/some/path/to/a/directory/on/the/DATABASE/SERVER';


Then the file will be created there.

http://asktom.oracle.com/~tkyte/flat/index.html

Michael, August 19, 2011 - 12:42 pm UTC

Hello Tom,
Culd you update C example link? We need such solution.
http://asktom.oracle.com/~tkyte/flat/index.html

Thank you

csv generator

chakrapani, September 09, 2011 - 6:57 am UTC

Tom,
I need to export data from a table in Oracle to SQL Server into a .csv file. I need to
have my column headers in between "$" and data separated by '#'.
Shall i expect any help from you.

thanking you in advance.
Tom Kyte
September 09, 2011 - 7:30 am UTC

you already have my help - you have 99.99% of everything - all you need to do is add the remaining 0.01%.


lines 21-25 print out the headings, you can modify it to suit your needs.


line 35 assigned my preferred delimiter, you can easily change that.

should take about 30-40 seconds I would guess :)

CSV Generator

chakrapani shroff, September 12, 2011 - 1:10 am UTC

Hi Tom,
I need to export data from a table in Oracle to SQL Server into a .csv file. I need to
have my column headers in between "$" and data separated by '#'.
Example Data
[city_name]|#|[std_code]|#|[city_code]|$|
NEW DELHI|#|5|#|0|$|
PUNE|#|5|#|0|$|

Thanking you in advance.
Tom Kyte
September 12, 2011 - 8:31 am UTC

ummm??? really?

csv generator

chakrapani shroff, September 12, 2011 - 5:28 am UTC

Hi Tom,

I have modified you code
plz let me know any more corrections

declare
p_tname varchar2(500):='&p_tname';
p_dir varchar2(20):='DIR_TEMP';
p_filename varchar2(500);
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000) default 'select * from ' || p_tname;
l_colCnt number default 0;
l_separator varchar2(3);
l_cnt number default 0;
l_descTbl dbms_sql.desc_tab;
begin
p_filename:=TRIM(p_tname) ||'.csv';
dbms_output.put_line('File Name :'||p_filename);
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
dbms_output.put_line('File Name :'||p_filename);
execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
utl_file.put( l_output, l_separator || l_descTbl(i).col_name || '');
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := '|#|';
end loop;
--utl_file.new_line(l_output );
UTL_FILE.PUT_LINE(l_output,'|$|');
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := '|#|';
end loop;
UTL_FILE.PUT_LINE(l_output,'|$|');
--utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
--raise;
end;
/

Tom Kyte
September 12, 2011 - 8:37 am UTC

why the HECK did you comment out the raise? Go back, read this page and see how many times I pointed out the "utter and complete lack of smartness" associated with that.


all you need to do now is test, test, test the code that you own. Make it your own.

Hint: corrections made over time would include a check in the exception block to see if the file is open and close it, to make sure to NOT comment out the raise, and consider the NLS_DATE_FORMAT - RR might be a better choice over YY.

Toufiq, October 19, 2011 - 2:28 am UTC

This thread helps me lot.

In my case i need not to create .csv file manually and writting into it, it should be automatically created with specific name into perticular directory.

can you please share your ideas how it will happen?

Tom Kyte
October 19, 2011 - 4:49 pm UTC

huh? I'm not really sure what you mean.

How can something be 'automatically' created (without you doing something to automate it???)

How can it have a specific name - unless you name it.


You'll need to define what you mean by "automatically" and "specific" here.

Toufiq, October 20, 2011 - 6:06 am UTC

I need to run a procedure daily at the end of day. This proc will extrct data from single table and store into csv file on server. name of file should be,
XXXX-999999-YYYYMMDDHHMMSS.gz
-XXXX = some thing i can not define. Its four chars of file name and it is fixed like "abcd"
-999999 = unique file sequence number for each file. Value of the sequence no will range from ‘000001’ TO ‘999999’.
-YYYYMMDDHHMMSS = File creation date and time
-.gz = gzip compression suffix. (Compression is an available option.)

how will I save file with this name?

Tom Kyte
October 20, 2011 - 7:59 am UTC

I need to run a procedure daily at the end of day.

you will use either dbms_job or dbms_scheduler to do that... that is easy.

name of file should be,
XXXX-999999-YYYYMMDDHHMMSS.gz


that'll be easy - write some plsql that will generate that name...


how will I save file with this name?


read about UTL_FILE, you might be able to use UTL_COMPRESS to create a compressed file depending on your version.


but you'll be writing a bit of code.

do learn about

dbms_job
dbms_scheduler
utl_file
utl_compress

CSV cursor

Developer-1, November 10, 2011 - 3:47 pm UTC

I am trying to get the CSV file out from a cursor but getting an error .Please help me correct the code. I am not sure i am getting the following error,

Error(232,3): PL/SQL: Statement ignored
Error(232,19): PLS-00221: 'PIO_CSTDN_EXTRACT_CUR' is not a procedure or is undefined

_____________________________________________________________

v_chr_sql := 'Select * from xxx';
OPEN pio_cstdn_extract_cur FOR v_chr_sql;

v_file := UTL_FILE.FOPEN(location => v_location ,
filename => v_file_name,
open_mode => 'w',
max_linesize => 32767);
FOR cur_rec IN pio_cstdn_extract_cur LOOP
UTL_FILE.PUT_LINE(v_file,
cur_rec.loan_id || ',' ||
-- cur_rec.ename || ',' ||
-- cur_rec.job || ',' ||
-- cur_rec.mgr || ',' ||
-- cur_rec.hiredate || ',' ||
-- cur_rec.empno || ',' ||
-- cur_rec.sal || ',' ||
-- cur_rec.comm || ',' ||
cur_rec.pool_id);
END LOOP;
UTL_FILE.FCLOSE(v_file);
___________________________________________________





Tom Kyte
November 10, 2011 - 5:55 pm UTC

sounds like you didn't define your cursor.


but without a FULL EXAMPLE (where you have already removed every other single line of code that isn't relevant to the problem at hand), we'll never know.


I am not a compiler.
I don't have all of your code
I don't want all of your code actually - just the BIT THAT DEMONSTRATES the issue.


Cursor

Developer, November 11, 2011 - 7:27 am UTC

PROCEDURE CUSTODIAN_LOAN_DML_SP (
pi_process_id IN NUMBER,
pi_servicing_number IN VARCHAR2,
pi_dml_action IN VARCHAR2,
po_sql_code OUT NUMBER,
po_return_msg OUT VARCHAR2) IS
-- execute dml for custodian in loan table
v_chr_db_pgm VARCHAR2(30) := 'custodian_loan_dml_sp';
v_chr_process_msg VARCHAR2(256) := v_chr_db_pgm||' being executed at '||to_char(SYSDATE,'mm-dd-yyyy-hh24:mi:ss');
BEGIN
--dbms_output.put_line('CHECKPOINT: '||v_chr_process_msg);
--po_sql_code := 0;
--po_return_msg := NULL;
IF TRIM(pi_dml_action) = C_DML_UPDATE AND pi_servicing_number IS NULL THEN
UPDATE powerseller.loan A
SET (A.fhlmc_loan_number) =
(select trim(I.fhlmc_loan_number)
from cms.cms_loan_import I
where I.process_id = pi_process_id
and I.loan_id = A.servicing_number)
WHERE EXISTS
(select J.loan_id
from cms.cms_loan_import J
where J.process_id = pi_process_id
and J.loan_id = A.servicing_number);
ELSIF TRIM(pi_dml_action) = C_DML_UPDATE AND pi_servicing_number IS NOT NULL THEN
NULL;
ELSIF TRIM(pi_dml_action) = C_DML_INSERT THEN
NULL;
END IF;
--COMMIT; client app to control commit
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR
(-20010, 'Database exception in custodian_loan_dml_sp. Please contact Help Desk.');
dbms_output.put_line('EXCEPTION OTHERS - custodian_loan_dml_sp');
END; -- custodian_loan_dml_sp

PROCEDURE CUSTODIAN_EXTRACT_SP(
pi_pool_id_list IN VARCHAR2,
pio_cstdn_extract_cur IN OUT custodian_cursor_type) IS
v_num_sql_code NUMBER;
v_chr_return_msg VARCHAR2(400);
v_chr_db_pgm VARCHAR2(30) := 'custodian_extract_sp';
v_chr_sql VARCHAR2(32767);
v_location varchar2(1000):='C:\logs' ;
v_file_name varchar2(1000):='Extract_Details.txt' ;
v_file UTL_FILE.FILE_TYPE;

BEGIN
v_num_sql_code := 0;
IF pi_pool_id_list IS NULL THEN
RAISE BAD_PARAMETER_EXCEP;
END IF;
v_chr_sql := BUILD_SELECT_EXPRESSION_FNC('custodian_extract');
IF v_chr_sql IS NULL THEN
RAISE NO_DATA_EXCEP;
END IF;
v_chr_sql := 'SELECT ' || v_chr_sql || ' FROM powerseller.loan L, powerseller.gmacm_loan_search B'
|| ' WHERE L.servicing_number = B.servicing_number'
|| ' AND B.pool_name IN (SELECT column_value pool_name FROM THE'
|| ' (SELECT CAST(in_list_fnc(UPPER(''' || pi_pool_id_list || ''')) AS ListTableType) FROM DUAL))'
|| ' ORDER BY L.pool_name, L.servicing_number';
OPEN pio_cstdn_extract_cur FOR v_chr_sql;
-- CUR_OUTPUT_CSV_SP ('Extract_details.txt', pio_cstdn_extract_cur);

v_file := UTL_FILE.FOPEN(location => v_location ,
filename => v_file_name,
open_mode => 'w',
max_linesize => 32767);
FOR cur_rec IN pio_cstdn_extract_cur LOOP
UTL_FILE.PUT_LINE(v_file,
cur_rec.loan_id || ',' ||
-- cur_rec.ename || ',' ||
-- cur_rec.job || ',' ||
-- cur_rec.mgr || ',' ||
-- cur_rec.hiredate || ',' ||
-- cur_rec.empno || ',' ||
-- cur_rec.sal || ',' ||
-- cur_rec.comm || ',' ||
cur_rec.pool_id);
END LOOP;
UTL_FILE.FCLOSE(v_file);

EXCEPTION
WHEN BAD_PARAMETER_EXCEP THEN
RAISE_APPLICATION_ERROR(-20002, 'Database exception in ' || v_chr_db_pgm || '. Please contact Help Desk.');
dbms_output.put_line('BAD_PARAMETER_EXCEP ' || v_chr_db_pgm);
WHEN NO_DATA_EXCEP THEN
RAISE_APPLICATION_ERROR(-20002, 'Database exception in ' || v_chr_db_pgm || '. Please contact Help Desk.');
dbms_output.put_line('NO_DATA_EXCEP ' || v_chr_db_pgm);
WHEN OTHERS THEN
v_num_sql_code := SQLCODE;
v_chr_return_msg := SUBSTR(SQLERRM, 1, 200);
ROLLBACK;
xml_import_common_pkg.xml_process_log_sp (c_num_process_id, NULL, C_POOL_STATUS_CUSTODIAN, C_STATUS_ERROR,
v_chr_db_pgm, Substr(Nvl(v_chr_return_msg,'msg') || ' ' || Nvl(v_chr_sql, 'sql'),1,2000), v_num_sql_code);
RAISE_APPLICATION_ERROR(-20002, 'Database exception in ' || v_chr_db_pgm || '. Please contact Help Desk.');
dbms_output.put_line('EXCEPTION OTHERS - custodian_extract_sp');
dbms_output.put_line(v_chr_return_msg);
END; -- CUSTODIAN_EXTRACT_SP

Cursor

Developer, November 11, 2011 - 7:37 am UTC

I have defined it as TYPE custodian_cursor_type IS REF CURSOR;
Tom Kyte
November 11, 2011 - 8:41 am UTC

you have clearly missed this point, it is very important:

I don't want all of your code actually - just the BIT THAT DEMONSTRATES the issue.



You sort of need to come up with an example that replicates your problem - that is 100% complete (it would include any create tables - teeny tiny SMALL - SO SMALL THAT THEY ONLY have the columns ABSOLUTELY NECESSARY to reproduce the issue) yes concise.

concise as in teeny tiny, small, just enough code to reproduce the issue.

It is called debugging.

It is an art that if you master - you will be so much more successful in your career - because if you can do it, you'll find and fix 99.99% of your problems yourself.

You cannot expect someone to read a large procedure like that, compile it in their head and point out your mistake.



I'll help you out on this one, but only because it accidentally hit me in the nose.

Here is the test case you SHOULD HAVE posted to demonstrate the issue:

ops$tkyte%ORA11GR2> create or replace procedure p( p_cur in out sys_refcursor )
  2  as
  3  begin
  4          open p_cur for 'select * from dual';
  5  
  6          for c in p_cur
  7          loop
  8                  null;
  9          end loop;
 10  end;
 11  /

Warning: Procedure created with compilation errors.

ops$tkyte%ORA11GR2> show err
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/2      PL/SQL: Statement ignored
6/11     PLS-00221: 'P_CUR' is not a procedure or is undefined
ops$tkyte%ORA11GR2> 



this is what you would have gotten down to in the end - after removing all of the non-essential bits.

Once you did that - many people would be able to point out in a millisecond that "you have an explicit ref cursor that you are trying to process implicitly - you cannot do that"

If you use OPEN, you cannot use "for x in cursor", you have to explicitly fetch from it. With a ref cursor - the ONLY way to do it is to explicitly fetch from it.



I don't know why you have your cursor defined as a parameter though. The client is not sending you a result set (the cursor is empty on the way into the procedure) and this routine would ALWAYS send an empty cursor back (the client wouldn't receive anything useful). Why is this a parameter?


This is what that code should resemble:




ops$tkyte%ORA11GR2> create or replace procedure p
  2  as
  3      p_cur  sys_refcursor;
  4  
  5      l_c1   sys.odciVarchar2List;
  6      l_c2   sys.odciVarchar2List;
  7      l_c3   sys.odciVarchar2List;
  8  
  9      l_limit number := 10;
 10  begin
 11  
 12      open p_cur for 'select ename, empno, sal from scott.emp';
 13  
 14      loop
 15          fetch p_cur bulk collect into l_c1, l_c2, l_c3 limit l_limit;
 16  
 17          for i in 1 .. l_c1.count
 18          loop
 19              dbms_output.put_line( l_c1(i) || ',' || l_c2(i) || ',' || l_c3(i) );
 20          end loop;
 21          exit when p_cur%notfound;
 22      end loop;
 23  
 24      close p_cur;
 25  end;
 26  /

Procedure created.

ops$tkyte%ORA11GR2> exec p
SMITH,7369,800
ALLEN,7499,1600
WARD,7521,1250
JONES,7566,2975
MARTIN,7654,1250
BLAKE,7698,2850
CLARK,7782,2450
SCOTT,7788,3000
KING,7839,5000
TURNER,7844,1500
ADAMS,7876,1100
JAMES,7900,950
FORD,7902,3000
MILLER,7934,1300

PL/SQL procedure successfully completed.



You want to set l_limit to about 100, I set to 10 for demonstration purposes - to show it "works" (I've have to test with more records than my limit clause to show it works)


Cursor

Developer, November 11, 2011 - 9:28 am UTC

Tom,
All the points mentioned above are noted, I will try to debug as you suggested.
my select script from v_chr_sql is giving me data but running into exceptions at fetch pio_cstdn_extract_cur bulk collect into l_c1, l_c2, l_c3 limit l_limit;

$Oracle.EXCEPTION_ORA_20002:


PROCEDURE CUSTODIAN_EXTRACT_SP(
pi_pool_id_list IN VARCHAR2,
pio_cstdn_extract_cur IN OUT custodian_cursor_type) IS
v_num_sql_code NUMBER;
v_chr_return_msg VARCHAR2(400);
v_chr_db_pgm VARCHAR2(30) := 'custodian_extract_sp';
v_chr_sql VARCHAR2(32767);
v_location varchar2(1000):='C:\logs' ;
v_file_name varchar2(1000):='Extract_Details.txt' ;
v_file UTL_FILE.FILE_TYPE;
l_c1 sys.odciVarchar2List;
l_c2 sys.odciVarchar2List;
l_c3 sys.odciVarchar2List;
l_limit number := 1000;

BEGIN
v_num_sql_code := 0;
IF pi_pool_id_list IS NULL THEN
RAISE BAD_PARAMETER_EXCEP;
END IF;
v_chr_sql := BUILD_SELECT_EXPRESSION_FNC('custodian_extract');
IF v_chr_sql IS NULL THEN
RAISE NO_DATA_EXCEP;
END IF;
v_chr_sql := 'SELECT ' || v_chr_sql || ' FROM powerseller.loan L, powerseller.gmacm_loan_search B'
|| ' WHERE L.servicing_number = B.servicing_number'
|| ' AND B.pool_name IN (SELECT column_value pool_name FROM THE'
|| ' (SELECT CAST(in_list_fnc(UPPER(''' || pi_pool_id_list || ''')) AS ListTableType) FROM DUAL))'
|| ' ORDER BY L.pool_name, L.servicing_number';
dbms_output.put_line(v_chr_sql);
OPEN pio_cstdn_extract_cur FOR v_chr_sql;
loop
fetch pio_cstdn_extract_cur bulk collect into l_c1, l_c2, l_c3 limit l_limit;
for i in 1 .. l_c1.count
loop
dbms_output.put_line( l_c1(i) || ',' || l_c2(i) || ',' || l_c3(i) );
end loop;
exit when pio_cstdn_extract_cur%notfound;
end loop;
close pio_cstdn_extract_cur;


EXCEPTION
WHEN BAD_PARAMETER_EXCEP THEN
dbms_output.put_line('BAD_PARAMETER_EXCEP ' || v_chr_db_pgm);
RAISE_APPLICATION_ERROR(-20002, 'Database exception in ' || v_chr_db_pgm || '. Please contact Help Desk.');
dbms_output.put_line('BAD_PARAMETER_EXCEP ' || v_chr_db_pgm);
WHEN NO_DATA_EXCEP THEN
RAISE_APPLICATION_ERROR(-20002, 'Database exception in ' || v_chr_db_pgm || '. Please contact Help Desk.');
dbms_output.put_line('NO_DATA_EXCEP ' || v_chr_db_pgm);
WHEN OTHERS THEN
v_num_sql_code := SQLCODE;
v_chr_return_msg := SUBSTR(SQLERRM, 1, 200);
ROLLBACK;
xml_import_common_pkg.xml_process_log_sp (c_num_process_id, NULL, C_POOL_STATUS_CUSTODIAN, C_STATUS_ERROR,
v_chr_db_pgm, Substr(Nvl(v_chr_return_msg,'msg') || ' ' || Nvl(v_chr_sql, 'sql'),1,2000), v_num_sql_code);
RAISE_APPLICATION_ERROR(-20002, 'Database exception in ' || v_chr_db_pgm || '. Please contact Help Desk.');
dbms_output.put_line('EXCEPTION OTHERS - custodian_extract_sp');
dbms_output.put_line(v_chr_return_msg);
END; -- CUSTODIAN_EXTRACT_SP
Tom Kyte
November 11, 2011 - 10:52 am UTC

yes, well, I have no idea what 20002 is, since that is a number YOU made up.

once again

a test case you give to another human being will be

a) SELF CONTAINED (meaning, it is complete, meaning they can actually run it in their database. We cannot run your code, can we? We are missing more than we are given)

b) TEENY TINY - there is a ton of code in there that is not relevant to the problem at hand isn't there. For example, everything - 100% EVERYTHING from the word "exception down to but not including the END; statement - isn't relevant. It shouldn't be there for your test case. And in fact, if you got rid of it during your debugging, instead of seeing a useless "ora-20002", you would see a very very very VERY useful *actual* error code AND error message!! You'd actually know 1) what the error was and 2) WHERE the error actually happened, the actual line number.

c) and in case it wasn't clear - the test case would be small and self contained.

d) see c again and again.


Just like the examples I give you guys all day long, every day, day in day out.



Toufiq, November 16, 2011 - 3:28 am UTC

Hi Tom,

procedure created sucessfully. but while executing from Client SQL developer I am getting this,

Error starting at line 1 in command:
execute dump_table_to_csv('emp', '/abcd/', 'abc.csv');
Error report:
ORA-29280: invalid directory path
ORA-06512: at "SCOTT.DUMP_TABLE_TO_CSV", line 44
ORA-06512: at line 1
29280. 00000 - "invalid directory path"
*Cause: A corresponding directory object does not exist.
*Action: Correct the directory object parameter, or create a corresponding
directory object with the CREATE DIRECTORY command.

following directory is created on server,
create or replace directory abcd as 'D:\oracle';

executing following command from Client SQL Developer,
execute dump_table_to_csv('emp', '/abcd/', 'abc.csv');

Is there any issue in passing parameters?
Tom Kyte
November 16, 2011 - 9:50 am UTC

please read about directory objects.

create or replace directory MY_DIR as '/abcd';

and then you would use 'MY_DIR' as the input to the procedure.


(the original procedure way above was written during 8i - before directory objects and use the deprecated, not to be used, UTL_FILE_DIR init.ora parameter - do not use this, use a directory object!!)


Query for CSV

Toufiq, November 27, 2011 - 11:06 pm UTC

Thanks Tom,

Procedure runs successfully.
Now I am facing problem while passing query. table contains two columns. Account Id and Bank Account Id
and data is,
Account Id Bank Acct ID
200001 AA1111111
200001 AA1111111
200001 AA1111113
200004 AA1111114
200004 AA1111114
200006 AA1111116
200007 AA1111116
200003 AA1111118

I need to result as
Account Id Bank Acct ID
200004 AA1111114
200004 AA1111114
200003 AA1111118

Fetch, bank acct Id used for only one Account Id and One Account Id used for only one Bank Acct ID.
Tom Kyte
November 29, 2011 - 7:12 am UTC

select account_id, max(bank_acct_id)
from t
group by account_id
having count(distinct bank_acct_id) = 1;




Toufiq, November 29, 2011 - 10:55 pm UTC

Hi Tom,

Query looks good. but still nedd few modification.
current query returns
acct_id Bank_acct)id
200003 AA1111119
200004 AA1111114
200006 AA1111116
200007 AA1111116

here bank_acct_id AA1111116 is used for two acct_id (200006,200007). last two records should not come.

I think current query logic is acct_id having dist bank_acct_id. but we need vice versa too.

Please help.
Tom Kyte
November 30, 2011 - 7:13 am UTC

if you want me to write a query, you'll have to post a create table and inserts.

When you do that, restate the entire question in full, in text, as a specification so I don't have to page up and down and digest three reviews to figure out what is needed.

help me out

sameul, December 16, 2011 - 1:31 am UTC

SQL> create or replace procedure dump_table_to_csv( book in varchar2,
  2                                                     F:\Rajesh\ORACLE_STUFF   in varchar2
  3                                                     book in varchar2 )
  4      is
  5          l_output        utl_file.file_type;
  6          l_theCursor     integer default dbms_sql.open_cursor;
  7          l_columnValue   varchar2(4000);
  8          l_status        integer;
  9          l_query         varchar2(1000)
 10                         default 'select * from ' || book;
 11         l_colCnt        number := 0;
 12         l_separator     varchar2(1);
 13         l_descTbl       dbms_sql.desc_tab;
 14     begin
 15         l_output := utl_file.fopen( F:\Rajesh\ORACLE_STUFF, Book1.txt, 'w' );
 16         execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' 
 17  ';
 18     
 19         dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
 20         dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 21     
 22         for i in 1 .. l_colCnt loop
 23             utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' 
 24  );
 25             dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
 26             l_separator := ',';
 27         end loop;
 28         utl_file.new_line( l_output );
 29     
 30         l_status := dbms_sql.execute(l_theCursor);
 31     
 32         while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
 33             l_separator := '';
 34             for i in 1 .. l_colCnt loop
 35                 dbms_sql.column_value( l_theCursor, i, l_columnValue );
 36                 utl_file.put( l_output, l_separator || l_columnValue );
 37                 l_separator := ',';
 38             end loop;
 39             utl_file.new_line( l_output );
 40         end loop;
 41         dbms_sql.close_cursor(l_theCursor);
 42         utl_file.fclose( l_output );
 43     
 44         execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
 45     exception
 46         when others then
 47             execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
 48             --raise;
 49     end;
 50     /

Warning: Procedure created with compilation errors.

SQL> exec dump_table_to_csv( 'book', 'F:\Rajesh\ORACLE_STUFF', 'book');
BEGIN dump_table_to_csv( 'book', 'F:\Rajesh\ORACLE_STUFF', 'book'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object RAJESH.DUMP_TABLE_TO_CSV is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Tom Kyte
December 16, 2011 - 5:33 pm UTC

type

sql> show errors procedure dump_table_to_csv


and fix them like you would for any of your code? What do you do when you write your own code and it doesn't compile???


watch out for word wrapping up there

Unloading the result of a query instead of whole table

Mehmet, January 15, 2012 - 3:26 pm UTC

Hi Tom, thank you for sharing your knowledge with us.

What if we want to unload the result of a query instead of the whole table?

Instead of whole employees table,

select * from employees where employee_id=100

Tom Kyte
January 17, 2012 - 1:40 pm UTC

you would change the code to take a query as input instead of just a tablename - that would be one approach.

Fast Enough and Robust

Jerry, February 01, 2012 - 8:53 am UTC

Thanks for the code. It got me pointed in the right direction with little time.

Extract data

A reader, March 08, 2012 - 12:20 am UTC

hi Tom,

I have to extract the data from the table which has around 5 million rows. What could be the best method to extract the data?

1. through SQL Plus spooling
2. through util_file
3. Any others

Another issue with the approaches:


I have a different server where I have to spool data. The Oracle server is on a different Unix box.

Possible solution I have:

1. We can have a NFS mount point on the oracle server pointing towards the Server where I have to spool data. We can create a directory object for read write

2. We can have SQL Plus installed on the FTP server and then can spool the data.

Could you please suggest what approach will be best considering both conditions.

Thanks for your time and help.
Tom Kyte
March 08, 2012 - 5:45 am UTC

it probably would not be the best idea to use plsql to write 5,000,000 rows out.

I would be tempted to have a developer code up a production application for this, but sqlplus would work in a pinch (no real error handling there).


utl file with explicit cursor

Jonie, March 20, 2012 - 9:15 am UTC

hi , if i modify the procedure , use explicit cursor to fetch records from table and print in text file (Using cursor attribute % Not found to terminate loop) and counter variable to count number of lines printed in the file ," I get a blank line in the end of the file ".How to avoid writing this blank line at the end of file.
ex:
1 ABC
2 DEF
3 PQR
BLANK LINE
I want the cursor after PQR and not that blank line in the end.
Tom Kyte
March 20, 2012 - 9:53 am UTC

show your work. I don't know how to fix invisible code.

make the code as small as possible, remove anything NOT RELEVANT to the problem at hand

code

Jonie, March 20, 2012 - 10:10 am UTC

Oops,
Here is the format below
v_count integer:=0;
OPEN C1_EXP;
LOOP
FETCH C1_EXP INTO V_INSERT_STRING;
EXIT WHEN C1_EXP%NOTFOUND;
UTL_FILE.PUTLINE(file_name,'%s\n',V_INSERT_STRING);
v_count:=v_count+1;
END LOOP;
UTL_FILE.FCLOSE(FILE_NAME);
CLOSE C1_EXP;

I have used a counter variable to count the number of records printed in the file.
The file generated prints a blank line feed at the end of the file.Please suggest n thanxs for prompt reply Tom.
Tom Kyte
March 20, 2012 - 3:02 pm UTC

utl_file doesn't have a putline procedure

maybe you meant putf


using put_line and '\n' would create two lines if it worked ;)


There isn't a blank line in your file, it is just that the last line has a carriage return on it. Are you *SURE* you don't want that there - most things would demand it be there.

If you really don't want it there then use utl_file.put and use utl_file.newline to put out a newline only when you want to.


You should loop like this:
for x in c1_exp
loop
    if ( v_count > 0 ) 
    then 
        utl_file.newline; 
    end if;
    utl_file.put( file_handle, x.string );
    v_count := v_count+1;
end loop;


that'll implicitly array fetch 100 rows at a time (do not use open/fetch/close for something like this, use an implicit cursor for loop!!!)

it'll only put out a new line before writing a line, starting after the first line is already put out.

Comment

Burrito Warrior, September 26, 2012 - 2:02 pm UTC

You are a saint. I have just read all of the comments/requests for help on this page.

Half of these questions are the same thing over and over again. I've just started working with Oracle PL/SQL, so by no means am I a genius.

But some of these questions are common sense.

Oh and by the way, did I tell you you're a saint?
Tom Kyte
September 27, 2012 - 8:36 am UTC

the older I get the less saintly I've become ;)

Tom,

abdul, October 03, 2012 - 8:48 am UTC

we want to import oracle data into csv file so that later we can applied it on sql server. we have used below procedure to create csv file ,every thing is fine but the problem is that when we open csv file with excel the data in it is not in consistent format

procedure is-------


create or replace procedure dump_table_to_csv( p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2 )
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000)
default 'select * from ' || p_tname;
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w');
execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss''
';

dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"'
);
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := '|';
end loop;
utl_file.new_line( l_output );

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := '|';
end loop;
utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );

execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise;
end;
/

exec dump_table_to_csv( 'PCA_RATE_REPORT_DATA_MIN_AUG', '/PGHProdDB/','tom.csv' );





then the output is ----------------------------->






Deluxe Accommodations - 1 King bed ) 3534820
1736456 677 02-aug-2012 00:00:00 24-aug-2012 12:00:00 5835 Brand Unqualified 1 1 1 1 USD 199 1 199 Non-Refundable Rate-Non-Refundable - Petite Room 3534820
1736459 493 02-aug-2012 00:00:00 05-sep-2012 12:00:00 98532 Galileo Auto Club 1 1 0 1 USD 452 1 452 1Q BED : AAA BREAKFAST PACKAGE.AAA OR CAA MEMBERSHIP ID REQUIRED AT CHECK-IN.TRADITIONAL NON-SMOKING:SWEET SLEEPER BED. 3534821
1736456 677 02-aug-2012 00:00:00 16-aug-2012 12:00:00 3695 Brand Unqualified 1 1 0 1 USD 324 1 324 Advance Purchase Promotion-Deluxe ( 271 sq ft room contemporary d&eacute cor, 37'' flat screen TV, granite-marble bath.

Deluxe Accommodations - 2 Double beds ) 3534820
1736459 493 02-aug-2012 00:00:00 16-oct-2012 12:00:00 98532 Galileo Unqualified 1 1 0 1 USD 459 1 459 1K BED : PREPAID RATE.SUPERIOR NON-SMOKING:SWEET SLEEPER BED:NEWLY.RENOVATED:HIGH FLOOR ROOM:FREE BOTTLED WATER. 3534821
1736456 677 02-aug-2012 00:00:00 25-sep-2012 12:00:00 3695 Brand Unqualified 0 1 0 1 USD 689 1 689 Ooh La La Romance Package-Deluxe ( 271 sq ft room contemporary d&eacute cor, 37'' flat screen TV, granite-marble bath.







Tom Kyte
October 09, 2012 - 11:53 am UTC

.. with excel the data in it is
not in consistent format ...


what does that mean exactly.


(this is a rather simple routine that creates a rather simple file, cannot you explain in some real detail what the issue is????)

abdul, October 10, 2012 - 1:11 am UTC

HI Tom ,
I am spooling bunch of data with .csv extention .when i tried to open it with libre office calc /ms excel it is not
showing output in proper format means if table have column name --BID

_BID_

1
2
3
4
5
Premier Suite - 2 Double sheets
6
7
8
Deluxe Suite - 1 King sheets )
9
10

so on..


some rows showing the contents of another column within BID column
please excuse if i am mistaken !
i have got a piece of knowledge elsewhere and would like to share that is:
"The problem is not in the file that's written, the problem is in the tool that's used to open it.
My version of Excel uses by default the semicolon ";" as separator character for csv files".
so I Have tried that:::

create or replace procedure xyz
as
v_file_handle1 utl_file.file_type;
v_filename varchar2(80CHAR);

begin
v_filename := 'xyz__'||to_char (sysdate, 'YYYYMMDDHH24MISS')|| '.csv';

-- Open flat files
v_file_handle1 := utl_file.fopen ('<YOUR_SERVER_DIRECTORY>',v_filename,'W',32000);

-- CHR(124) represents => 'slash'
utl_file.put_line (v_file_handle1,
'UNIVERSALID'||chr(124)||
'BID'||chr(124)||
'SHOP_DATE'||chr(124)||
'CHECK_IN_DATE'||chr(124)||
'PROPERTY_ID'||chr(124)||
'GDS_HOST_CODE'||chr(124)||
'RATE_CATEGORY'||chr(124)||
'FENCE_INDICATOR'||chr(124)||
'MINIMUM_LENGTH_OF_STAY'||chr(124)||
'MERCHANT_RATE_INDI'||chr(124)||
'NUMBER_OF_GUESTS'||chr(124)||
'CURRENCY_CODE'||chr(124)||
'ROOM_RATE'||chr(124)||
'EXRATE'||chr(124)||
'USD_RATE'||chr(124)||
'RATE_DESCRIPTION'||chr(124)||
'REPORT_REQUEST_OID');

for v_cursor in (select
universalid,
bid,
shop_date,
check_in_date,
property_id,
gds_host_code,
rate_category,
fence_indicator,
minimum_length_of_stay,
merchant_rate_indi,
number_of_guests,
currency_code,
room_rate,
exrate,
usd_rate,
rate_description,
report_request_oid
from pace.pca_rate_report_data_min_aug where rownum <100000)
loop
--Writes the data in to the file
utl_file.put_line (v_file_handle1,
'"'||v_cursor.universalid||'"'||chr(124)
||'"'||v_cursor.bid||'"'||chr(124)
||'"'||v_cursor.shop_date||'"'||chr(124)
||'"'||v_cursor.permid||'"'||chr(124)
||'"'||v_cursor.check_in_date||'"'||chr(124)
||'"'||v_cursor.property_id||'"'||chr(124)
||'"'||v_cursor.gds_host_code||'"'||chr(124)
||'"'||v_cursor.rate_category||'"'||chr(124)
||'"'||v_cursor.minimum_length_of_stay||'"'||chr(124)
||'"'||v_cursor.merchant_rate_indi||'"'||chr(124)
||'"'||v_cursor.number_of_guests||'"'||chr(124)
||'"'||v_cursor.currency_code||'"'||chr(124)
||'"'||v_cursor.room_rate||'"'||chr(124)
||'"'||v_cursor.exrate||'"'||chr(124)
||'"'||v_cursor.usd_rate||'"'||chr(124)
||'"'||v_cursor.rate_description||'"'||chr(124)
||'"'||v_cursor.report_request_oid||'"');
end loop;
utl_file.fclose (v_file_handle1);
end;

execute xyz;

hence done the trick :)

Need for speed

Vincent, May 23, 2013 - 1:06 pm UTC

Hello Tom,

we need to produce export files on csv format, on big tables, as quick as possible !

You talked about a solution written in C, but the link is no more available ( http://asktom.oracle.com/~tkyte/flat/index.html )

What solution do you advice for this need for speed ?

Thanks in advance
Tom Kyte
May 23, 2013 - 3:14 pm UTC

http://asktom.oracle.com/Misc/httpasktomoraclecomtkyteflat.html


all the methods are there, sqlplus is "pretty darn fast" if you cannot get the pro*c going.

Java Program for exporting/loading data between Database and CSV

Manohar, May 23, 2013 - 5:36 pm UTC

https://sourceforge.net/projects/databasetocsv/

Using this Java program,

1) you can take data backup of Database in CSV files.

2) Load data from csv to database

Note: Supports Oracle, MySQL and SQL Server

Very useful

Eshwar, June 21, 2013 - 8:08 am UTC


this link helped me but i am stuck with one isue here it is working for all the tables with datatype except for BLOB. How to handle this in the function?

Thanks,
Eshwar.
Tom Kyte
July 01, 2013 - 3:57 pm UTC

what would you like to do with the blob?? How would you like to output it? In what format (and once you spec this all out - you'll have the details you need to write a subroutine to take a lob locator and dump it out of the file!)

Worked!!

Beatriz, November 21, 2013 - 8:19 am UTC

Hey! I had some issues but finally, after making the necessary to have the file_utl working and setting some privileges in the directory, it worked! I had to do:
GRANT READ, WRITE ON DIRECTORY ORA_DIR TO client_1;
(...change the user...)
exec dump_table_to_csv( 'daa_control', 'ORA_DIR', 'control.csv' );

describe two ways to connect oracle client to oracle server

khan, February 28, 2014 - 11:27 am UTC

plz tell

CSV unfriendly data type exclusion

Alex, March 14, 2014 - 8:19 am UTC

I have small enhancement of original procedure. This enhancement exclude columns that have "CSV unfriendly" data types like BLOB, SDO_GEOMETRY, ...
"col_type" codes could be found in SQL language reference, chapter 2, paragraph "Oracle Built-in Data Types"

. . .
21      for i in 1 .. l_colCnt loop
21.5        if l_descTbl(i).col_type in (1,2,8,12,180,96,112) then    
22              utl_file.put( l_output, l_separator || '"' || 
l_descTbl(i).col_name || '"' );
23              dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
24              l_separator := ',';
24.5        end if;
25      end loop;
26      utl_file.new_line( l_output );
27  
28      l_status := dbms_sql.execute(l_theCursor);
29  
30      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
31          l_separator := '';
32          for i in 1 .. l_colCnt loop
32.5            if l_descTbl(i).col_type in (1,2,8,12,180,96,112) then    
33                  dbms_sql.column_value( l_theCursor, i, l_columnValue );
34                  utl_file.put( l_output, l_separator || l_columnValue );
35                  l_separator := ',';
35.5            end if;
36          end loop;
37          utl_file.new_line( l_output );
38      end loop;
. . .

error message "table or view does not exist"

ibe, January 30, 2015 - 8:29 am UTC

Hi Tom,
Nice share you have here :)
I have created perl + sql script that do the same purpose as your procedure. Now I want to compare both of it, which one is more efficient since it will be run daily to export hundred of tables with huge ammount of data.

I have replicated your procedure on my server, it went well so far. The problem is while executing the procedure :

exec dump_table_to_csv ('drms_inventory','CSVDIR','test.csv');

it throws message that "table or view does not exist". meanwhile if you do regular query like :
select * from drms_inventory;

it shows the exact data of drms_inventory. test.csv is created whitin the spesified folder but nothing is being written on the file. I wonder if you have any idea what is going on.

Deep, March 26, 2015 - 9:46 am UTC

Thanks for the code, it really helped me.
I used the exactly same code as ur without any changes.. however getting an issue wherein file is being created but no data is being written to it. getting below error:-

exec dump_table_to_csv( 'test_bcacct', 'PURGE_LOGDIRNAME', 'DUMP1.CSV');
BEGIN dump_table_to_csv('test_bcacct', 'PURGE_LOGDIRNAME', 'DUMP1.CSV'); END;


Deep, March 26, 2015 - 9:48 am UTC

I think I was unable to paste the error in my last review.
Pasting again.

Error starting at line 1 in command:
exec dump_table_to_csv( 'test_bcacct', 'PURGE_LOGDIRNAME', 'DUMP1.CSV')
Error report:
ORA-29285: file write error
ORA-06512: at "EIPP2ARCH.DUMP_TABLE_TO_CSV", line 40
ORA-06512: at line 1
29285. 00000 - "file write error"
*Cause: Failed to write to, flush, or close a file.
*Action: Verify that the file exists, that it is accessible, and that
it is open in write or append mode.

Table or view does not exists

Vengadesh, June 13, 2015 - 3:17 pm UTC

Hi Tom,

I have created your procedure dump_table_to_csv in test and exported the table to file successfully. But the tables passed was my own schema's table. But if I do it for any other table of others schema, I m getting the error as table of view does not exist.

I have both sys athentication and my id have dba privilege.

I have tried created a public synonym but no use I have also
tried changing the procedure to

create or replace procedure dump_table_to_csv( p_owner in varchar2,
p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2 )
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000)
default 'select * from ' || p_owner||'.'||p_tname;
.......

But still I m not able to execute the procedure. Getting same error. Please help me with this.

tolupuluri, June 09, 2016 - 2:53 pm UTC

hi Tom,

Your procedure to export the data from oracle table to csv is working fine in my situation.
But if i tried to export the data from sample table with 5 columns and 10 records in total, it is working fine.
where as if i use the table with 600 columns and around 5000 records in total, it doesnt work.
the error is:

Error report:
ORA-29285: file write error
ORA-06512: at "MDMETL.DUMP_TABLE_TO_CSV", line 47
ORA-06512: at line 1
29285. 00000 - "file write error"
*Cause: Failed to write to, flush, or close a file.
*Action: Verify that the file exists, that it is accessible, and that it is open in write or append mode.
Chris Saxon
June 10, 2016 - 2:52 am UTC

Try change

l_output := utl_file.fopen( p_dir, p_filename, 'w' );

to

l_output := utl_file.fopen( p_dir, p_filename, 'w' ,32767);

to increase the allowable width of a line.

If your data is longer than 32767 bytes per line, then you are out of luck.

HACKINGSAVER, July 16, 2016 - 8:02 am UTC

THE SPEED ON THE DATABASS IS REALLY SLOW SPEED IT UP
ANT NO HACKEING LIKE WHAT JUST HAPPEN

Connor McDonald
July 17, 2016 - 12:23 am UTC

Set go_faster = true

Greater than 32767 bytes per line

Rajeshwaran, Jeyabal, July 17, 2016 - 11:50 am UTC

If your data is longer than 32767 bytes per line, then you are out of luck.

Why do we say "out of luck"? in case of more than 32767 bytes per line, does "piece wise access" to data don't help? something like this https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:68212348056#9529211800346862170
Connor McDonald
July 18, 2016 - 12:19 am UTC

It is not just a case of piecewise...

Because you'll still have to put it out with utl_file, which is capped at 32767, eg

--
-- 32000 line
--
SQL> declare
  2    f utl_file.file_type;
  3  begin
  4    f := utl_file.fopen('TEMP','demo.dat','w',32767);
  5    for i in 1 .. 32 loop
  6      utl_file.put(f,rpad('x',1000,'x'));
  7    end loop;
  8    utl_file.fclose(f);
  9  end;
 10  /

PL/SQL procedure successfully completed.
--
-- 33000 line
--
SQL> declare
  2    f utl_file.file_type;
  3  begin
  4    f := utl_file.fopen('TEMP','demo.dat','w',32767);
  5    for i in 1 .. 33 loop
  6      utl_file.put(f,rpad('x',1000,'x'));
  7    end loop;
  8    utl_file.fclose(f);
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 690
ORA-06512: at line 8




So that typically means you now have to do it using utl_file.put_raw (as opposed to the put/put_line). So its now *your* responsbility to deal with line returns (which isnt such a drama) but also characterset management etc (which can be harder).


Greater than 32767 bytes per line

Rajeshwaran, Jeyabal, July 18, 2016 - 2:55 am UTC

Tried this on my local, for more than 32K got this error.
looked around the docs, It has this

http://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS70940

...
The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. 
If unspecified, Oracle supplies a default value of 1024.
The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.
...


so tried even including fflush() calls, it doesn't help.
looks like it fails in the second iteration.

demo@ORA11G> declare
  2     f utl_file.file_type;
  3     l_str clob;
  4     l_offset int := 1;
  5     l_piece varchar2(32767);
  6  begin
  7     f := utl_file.fopen('TMP','demo.dat','w',32767);
  8     for i in 1..33
  9     loop
 10             l_str := l_str || rpad('x',1000,'x');
 11     end loop;
 12     if dbms_lob.getlength(l_str) <= 32765 then
 13             utl_file.put(f,l_str);
 14     else
 15             loop
 16                     exit when l_offset >= dbms_lob.getlength(l_str);
 17                     l_piece := dbms_lob.substr(l_str,32766,l_offset);
 18                     l_offset := l_offset + length(l_piece);
 19                     dbms_output.put_line(' Length ='|| length(l_piece));
 20                     utl_file.put(f,l_piece);
 21                     utl_file.fflush(f);
 22                     dbms_output.put_line(' Completed the Length ='|| length(l_piece));
 23             end loop;
 24     end if;
 25     utl_file.fclose(f);
 26  end;
 27  /
 Length =32766
 Completed the Length =32766
 Length =234
declare
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 183
ORA-06512: at "SYS.UTL_FILE", line 1169
ORA-06512: at line 21


demo@ORA11G>


Insted of utl_file.put, tried with dbms_ouput.put again the same issues

demo@ORA11G> declare
  2     l_str clob;
  3     l_offset int := 1;
  4     l_piece varchar2(32767);
  5  begin
  6     for i in 1..33
  7     loop
  8             l_str := l_str || rpad('x',1000,'x');
  9     end loop;
 10     if dbms_lob.getlength(l_str) <= 32767 then
 11             dbms_output.put(l_str);
 12     else
 13             loop
 14                     exit when l_offset >= dbms_lob.getlength(l_str);
 15                     l_piece := dbms_lob.substr(l_str,32767,l_offset);
 16                     l_offset := l_offset + length(l_piece);
 17                     dbms_output.put_line(' Length ='|| length(l_piece));
 18                     dbms_output.put(l_piece);
 19                     dbms_output.put_line(' Completed the Length ='|| length(l_piece));
 20             end loop;
 21     end if;
 22  end;
 23  /
 Length =32767
declare
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 32767 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 19


demo@ORA11G>


when tried to replace dbms_output.put(l_piece) with dbms_output.put_line(l_piece) then no errors.

demo@ORA11G> set serveroutput off
demo@ORA11G> declare
  2     l_str clob;
  3     l_offset int := 1;
  4     l_piece varchar2(32767);
  5  begin
  6     for i in 1..33
  7     loop
  8             l_str := l_str || rpad('x',1000,'x');
  9     end loop;
 10     if dbms_lob.getlength(l_str) <= 32767 then
 11             dbms_output.put(l_str);
 12     else
 13             loop
 14                     exit when l_offset >= dbms_lob.getlength(l_str);
 15                     l_piece := dbms_lob.substr(l_str,32767,l_offset);
 16                     l_offset := l_offset + length(l_piece);
 17                     dbms_output.put_line(' Length ='|| length(l_piece));
 18                     dbms_output.put_line(l_piece);
 19                     dbms_output.put_line(' Completed the Length ='|| length(l_piece));
 20             end loop;
 21     end if;
 22  end;
 23  /

PL/SQL procedure successfully completed.

demo@ORA11G>


any inputs/clue/help/workarounds to resolve this error introduced by having utl_file.put or dbms_output.put for length greater than 32767 ?
Connor McDonald
July 18, 2016 - 4:54 am UTC

You need to do it raw.

Greater than 32767 bytes per line

Rajeshwaran, Jeyabal, July 18, 2016 - 5:53 am UTC

Thanks it helps.

demo@ORA11G> declare
  2     f utl_file.file_type;
  3     l_str clob;
  4     l_offset int := 1;
  5     l_piece varchar2(32767);
  6  begin
  7     f := utl_file.fopen('TMP','demo.dat','wb',32767);
  8     for i in 1..33
  9     loop
 10             l_str := l_str || rpad('x',1000,'x');
 11     end loop;
 12     if dbms_lob.getlength(l_str) <= 32765 then
 13             utl_file.put(f,l_str);
 14     else
 15             loop
 16                     exit when l_offset >= dbms_lob.getlength(l_str);
 17                     l_piece := dbms_lob.substr(l_str,32766,l_offset);
 18                     l_offset := l_offset + length(l_piece);
 19                     dbms_output.put_line(' Length ='|| length(l_piece));
 20                     utl_file.put_raw(f,utl_raw.cast_to_raw(l_piece));
 21                     dbms_output.put_line(' Completed the Length ='|| length(l_piece));
 22             end loop;
 23     end if;
 24     utl_file.fclose(f);
 25  end;
 26  /
 Length =32766
 Completed the Length =32766
 Length =234
 Completed the Length =234

PL/SQL procedure successfully completed.

demo@ORA11G> $dir d:\app\demo.dat
 Volume in drive D is RAJESH
 Volume Serial Number is E079-099C

 Directory of d:\app

07/18/2016  11:21 AM            33,000 demo.dat
               1 File(s)         33,000 bytes
               0 Dir(s)  343,716,741,120 bytes free

demo@ORA11G>

my error not showed yet

Anton, July 29, 2016 - 11:16 am UTC

Hello, I have an error while exporting table to csv with described in this topic procedure:

ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 148
ORA-06512: at "SYS.UTL_FILE", line 403
ORA-06512: at "SYS.UTL_FILE", line 860
ORA-06512: at "HCA.DUMP_TABLE_TO_CSV", line 38
ORA-06512: at line 13

....
line 38: utl_file.new_line( l_output );
....


Exported table is about 500000 ordered string with about 2000 characters on exported string, no blobs or large data.

Error arises on same string every time, but if i change nunumber of exporting column (one or two minus), line of error is unpredictable changes.

Do you have any idea about this?
Thanks a lot.
Chris Saxon
August 01, 2016 - 1:58 pm UTC

What exactly is your code doing? Show us!

A reader, September 10, 2016 - 5:11 am UTC

I used utl_file function to create a csv file but file create on database server not client.
so how i can create file on client machine?
Connor McDonald
September 10, 2016 - 5:16 am UTC

You need a *client* program, or write the file to a location on the server that the client can see (eg an NFS share)

For a client program...Easiest way would be to Sql developer.

A reader, September 10, 2016 - 9:56 am UTC

I want to attach a csv file from external ( client machine ).
and my code is below :-

UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, v_htmlbody);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn,'Content-Disposition: attachment; filename="'|| 'd:\file_sale_report.csv' || '"'|| UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf);



and output display in the message part of email but file does not attach.
Content-Disposition: attachment;filename="file_sale_report.csv"

invalid directory path

William, January 13, 2017 - 5:26 pm UTC

hi,

I'm having a problem executing the procedure:

create or replace PROCEDURE ZP_TESTEPROCEDURE (
p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2)

is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000) default 'select nome, CPFALUNO from '|| p_tname || 'where nome like "%WILLIAM F%"';
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;

begin
l_output := utl_file.fopen( p_dir, p_filename, 'w','32760');
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := ',';
end loop;
utl_file.new_line( l_output );

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );

/*exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise;*/

END ZP_TESTEPROCEDURE;


exec ZP_TESTEPROCEDURE( 'NAME OF MY TABLE', 'NAME OF MY DIRECTORY', 'teste.csv' );

Relatório de erros -
ORA-29280: caminho de diretório inválido
ORA-06512: em "SYS.UTL_FILE", line 29
ORA-06512: em "SYS.UTL_FILE", line 448
ORA-06512: em "RM.ZP_TESTEPROCEDURE", line 17
ORA-06512: em line 1
29280. 00000 - "invalid directory path"
*Cause: A corresponding directory object does not exist.
*Action: Correct the directory object parameter, or create a corresponding
directory object with the CREATE DIRECTORY command.


*** Already checked if the directory was created and the permissions, apparently everything ok ***

Can you give me a hand? And sorry for my bad english :'(
Connor McDonald
January 14, 2017 - 7:50 am UTC

Shrink your procedure down to this

create or replace PROCEDURE ZP_TESTEPROCEDURE ( 
p_dir in varchar2, 
p_filename in varchar2) 
is 
l_output utl_file.file_type; 
begin 
l_output := utl_file.fopen( p_dir, p_filename, 'w','32760'); 
utl_file.put_line( l_output, 'Test'); 
utl_file.fclose( l_output ); 
END ZP_TESTEPROCEDURE; 


to ensure that your directory is fine.

Dont forget that the directory must exist and be writable by the *oracle database* account at the OS level. And that you have done:

grant read, write on directory MY_DIRECTORY to ....

from within the database

Help - SQL command not properly ended

William, January 25, 2017 - 4:19 pm UTC

Hello Connor,

I have a new problem, based on procedure that I quoted above, presents this new error:

ORA-00933: SQL command not properly ended
ORA-06512: em "SYS.DBMS_SYS_SQL", line 909
ORA-06512: em "SYS.DBMS_SQL", line 39
ORA-06512: em "RM.ZP_TESTEPROCEDURE", line 66
ORA-06512: em line 10

Line 66 is:
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );

Do you help me? Thanks!
Connor McDonald
January 26, 2017 - 11:49 pm UTC

Put a space between the quote and the WHERE

'select nome, CPFALUNO from '|| p_tname || 'where nome like "%WILLIAM F%"';

'select nome, CPFALUNO from '|| p_tname || ' where nome like "%WILLIAM F%"';

Alter Session NLS_LANGUAGE

William, February 23, 2017 - 1:39 pm UTC

Hello, I need to change the charset of my session.
Currently it looks like this:
NLS_LANGUAGE BRAZILIAN PORTUGUESE
NLS_TERRITORY BRAZIL
NLS_CHARACTERSET WE8ISO8859P1

I am trying to use the following command, but it returns an error.

Code:
...
v_arquivo := utl_file.fopen('C:\', 'auth.csv', 'W', '32760');
execute immediate 'alter session set nls_language=''BRAZILIAN PORTUGUESE_BRAZIL.UTF8''';
...

Error:
ORA-12705: Cannot access NLS data files or invalid environment specified

I need to do this charset exchange because the file is saving with the ANSI encode and I need it to be UTF8. Can you help me? Thank you very much.
Connor McDonald
February 25, 2017 - 1:24 am UTC

Check out MOS note 227531.1

It covers all the details of handling extended characters with utl_file

Jess, March 03, 2017 - 3:01 pm UTC

Hi Tom,

In terms of reading data from a few tables, pre-processing and assembling it into desired output, and writing a CSV file out to a different server...

There were a few comments about this in the thread, some referencing SQL Developer. What does one do if that's not a one time thing? We have a daily job (that needs to be scheduled, either on the client or in DB) that would produce a CVS file and leave it on the client. For config reasons I have no control over, writing a file on the server and pulling it from there is not acceptable--file needs to be created on the client.

SQL Developer just doesn't seem like the way to go here... But I'm slightly at a loss for what is... It should be possible (in theory) to call a PL/SQL procedure on the server that would assemble the output and return it as a collection that the client program could then take and write to a csv there, but not sure if or how that'd be doable (and SQL Developer doesn't seem to be that program--thinking more like custom Java application or something).

What do you recommend in this case? Running on 11gR1 with possibility of 12c upgrade.
Chris Saxon
March 03, 2017 - 3:58 pm UTC

Yes, using SQL Developer to get CSVs is really a one-off thing. It's not suitable for regular downloads.

If writing to the DB server then transferring to the client is off the table, then yes, you'll need to create something in your application tier. Look into whatever your current application technologies are and see if they can help you.

Jess, March 05, 2017 - 5:10 pm UTC

Hi Chris,

Our application technologies can be anything and everything. I am really trying to understand how an Oracle stored proc would be called remotely and send its output back to the calling app server where it can be written to a csv... I am simply not aware of how to do that (and searching around didn't turn up anything specific). Would you happen to have an example/s of how this kind of thing can be done?

Thanks in advance!

Connor McDonald
March 07, 2017 - 2:03 am UTC

If you need that file on an app server (or any server that is not the db server), then options are:

a) a shared file system (ie, db writes the file, app server can see the file)
b) a client tool (that runs on the app server), eg

- sqlplus (checkout the 'unloader' routine in the Resources tab)
- sqldeveloper (which can run queries in command line mode, so it doesnt have to be the full gui)
- your own program (eg Pro*C)

Export oracle table to CSV

Anand, June 30, 2017 - 7:22 am UTC

Hi Tom,

I need to export oracle table to csv and i am using oracle XE and Oracle sql developer.

The code given by you throws the following error. Could you please share the new PL/SQl procedure to to export the data to CSV.

ERROR:

Warning: Procedure created with compilation errors.

INE/COL ERROR
-------- -----------------------------------------------------------------
5/12 PL/SQL: Item ignored
5/12 PLS-00201: identifier 'UTL_FILE' must be declared
15/3 PL/SQL: Statement ignored
15/3 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

22/6 PL/SQL: Statement ignored
22/20 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

26/4 PL/SQL: Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
26/23 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

34/9 PL/SQL: Statement ignored
34/23 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

37/4 PL/SQL: Statement ignored
37/23 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

Connor McDonald
July 01, 2017 - 2:48 am UTC

If you have SQL Developer, it is far easier.

select /*csv*/ col1, col2, ... from my_table;

Links not woking

A reader, July 04, 2017 - 8:37 am UTC

Links are not Working
Plz update them.

A reader, July 20, 2017 - 9:52 am UTC

Hi,
i Executed below query
select /*csv*/ * from employees;
but i did not get dresult in CSV fromat, i got result format like i get with
select * from employees;
statement. in both statements i got CSV format in Export option, got after right clicking on Results.
also the CSV test is commented, so how sql developer understands that we need result in CSV fromat
Chris Saxon
July 20, 2017 - 1:01 pm UTC

And which version of SQL Developer are you using?

SQl Devloper Version

A reader, July 25, 2017 - 10:17 am UTC

4.1.5
Chris Saxon
July 25, 2017 - 2:23 pm UTC

And did you run your query in script mode (F5) or statement mode (F9)? It only works in script mode.

A reader, July 26, 2017 - 9:54 am UTC

Thanks chris i got it.
but i have few doubts about it
1. can't we export CSV output into excel.
2.i don't want double coats in result but i am getting
3.can we change list separator i am getting , but i want | is that possible?

Thanks in advance.
Chris Saxon
July 26, 2017 - 3:51 pm UTC

If you want more export options then right click the table in the schema browser and select export. There you'll find options to remove double quotes, change the delimiter, save as Excel, etc.

CSV Generation

Mehdi, October 25, 2020 - 8:08 am UTC

Really appreciated of the procedure it is really helpful but too bad it doesn't work with table with CLOB or having huge XML on it, if table is just having regular data its a life savor.

is there anyway that can export 15GB table with Huge CLOB using sqlplus in CSV format?
Connor McDonald
October 27, 2020 - 1:41 am UTC

Are there line feed or carriage returns in the clob? Because that's going to make CSV difficult no matter what.

unload table to csv with clob, blob and xml columns

Mark Wooldridge, October 27, 2020 - 9:25 pm UTC

I updated the procedure from Tom to unload clob, blob and xml data to files, the csv file contained a placeholder for the name of the file, I used a file name like table-name||column-name||primary-key of the record.

I would speculate large clob data is not something excel could handle, think there is a limit of 32,767.

Unloading clob, blob, and xml data to files allows for easily creating loader files that can be used to load the records back into tables.

More to Explore

DBMS_JOB

More on PL/SQL routine DBMS_JOB here