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

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

 

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 

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>  

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...  :~)
 

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

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.



 

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!

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;

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, could you please tell me what you meant with adding 32760 to l_output?

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

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.

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.

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

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

Excellent - most useful for views

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.

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;

Can the exportation from SQL to CSV be done using DBMS_Output instead of UTL.FILE?

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

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?

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

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

Sorry.. i still have problem in trying to save a specify filename...

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.

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

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

Hi Tom,

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

Thanks for helping everyone in need.

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.

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.

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!

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

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

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

 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;

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

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

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.

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

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

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 ?

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

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

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

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?

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

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

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;

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.

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;

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

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:

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:

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?

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.

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

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

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

grant execute on UTL_FILE to public;

after execute the above command
procedure will working successfully.

Regards
hitgon

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

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

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.

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

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

Thank you

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.

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.

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

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?

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?

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);
___________________________________________________

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

I have defined it as TYPE custodian_cursor_type IS REF CURSOR;

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> 

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.

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

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?

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.

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.

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

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

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

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.

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.

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.

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;

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?

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.

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

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

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

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.

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' );

plz tell

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

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.

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;

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.

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.

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.

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

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

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

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 ?

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>

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.

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?

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"

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 :'(

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; 

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!

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.

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.

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!

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

Links are not Working
Plz update them.

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

4.1.5

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.

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?

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.

would like to export a table containing CLOB columsn to a delimited file per below specs

FIELDS TERMINATED BY '|^' LINES TERMINATED BY '#@%'

I am using the the PL/SQL function export_table_to_csv_file in Doc ID 1967617.1 and I am able to use '|^' as the field delimiter but I also need to have '#@%' at the end of each row as a record delimiter/line terminator.

Currently below usage gives '|^' as the field delimiter .. I just need to know how how to modify the code to add '#@%' at the end of each row as a record delimiter/line

set serverout on

DECLARE

l_sql dbms_sql.varchar2a;

l_cnt integer;

BEGIN

l_sql(1) := 'SELECT /*+parallel (16)+*/ * from dra_owner.claim_data_mm';

l_cnt := export_table_to_csv_file

(

l_sql,

'|^',

'TOSINGLESTORE',

'claim_data_exp2.dsv',

TRUE

);

END;

/

utl_file.new_line( l_output );

utl_file.put(l_output,'#@%' );

CREATE OR REPLACE PROCEDURE APPS.XX_CSV
AS
CURSOR c_data IS
SELECT TAG,
MEANING,
DESCRIPTION,
LOOKUP_TYPE,
LOOKUP_CODE,
ENABLED_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
CREATION_DATE
FROM apps.FXX_XXX_XXXS_VL
WHERE lookup_type = 'XX_';

v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file :=
UTL_FILE.FOPEN (location => '/u02/XXDEV/XXXtmp',
filename => 'lookup_csv.csv',
open_mode => 'w',
max_linesize => 32767);

UTL_FILE.PUT_LINE (v_file,
'TAG'
|| ','
|| 'MEANING'
|| ','
|| 'DESCRIPTION'
|| ','
|| 'LOOKUP_TYPE'
|| ','
|| 'LOOKUP_CODE'
|| ','
|| 'ENABLED_FLAG'
|| ','
|| 'ATTRIBUTE_CATEGORY'
|| ','
|| 'ATTRIBUTE1'
|| ','
|| 'CREATION_DATE');


FOR cur_rec IN c_data
LOOP
UTL_FILE.PUT_LINE (
v_file,
cur_rec.TAG
|| ','
|| cur_rec.MEANING
|| ','
|| cur_rec.DESCRIPTION
|| ','
|| cur_rec.LOOKUP_TYPE
|| ','
|| cur_rec.LOOKUP_CODE
|| ','
|| cur_rec.ENABLED_FLAG
|| ','
|| cur_rec.ATTRIBUTE_CATEGORY
|| ','
|| cur_rec.ATTRIBUTE1
|| ','
|| cur_rec.CREATION_DATE);
END LOOP;

UTL_FILE.FCLOSE (v_file);
EXCEPTION
WHEN OTHERS
THEN
UTL_FILE.FCLOSE (v_file);
RAISE;
END;
/



Running fine..
Thanks
k