output from stored procedure to a file

Hello everyone,

I am trying to write the output of a stored procedure to a flat file at the OS level, preferably .csv.  What is the right procedure to do so? or is there a better way to export the data from this stored procedure to be used elsewhere?

 I am running Oracle 9i on a win2k server.

My stored procedure works and I'm able to use the DBMS_OUTPUT.PUT_LINE to print the output to the screen; however, it has a 255 character per line limitation and I can't get past it.

say I have the following stored procedure:


----------------------8<------------------------------------
CREATE OR REPLACE PROCEDURE GET_CUSTOMERS
     ( p_month IN varchar2)
AS
CURSOR p_customer_cursor is
   
SELECT ...
FROM  ...
WHERE ...
Group By ...
Order by ...

rec_customers    p_customer_cursor%ROWTYPE;

BEGIN
open p_customer_cursor;
     LOOP
         FETCH p_customer_cursor INTO rec_customers;
             EXIT WHEN p_customer_cursor%NOTFOUND;

         DBMS_OUTPUT.PUT_LINE(  rec_customers.column1 ..... );
             
     END LOOP;
     CLOSE p_customer_cursor;
END GET_CUSTOMERS;
/

----------------------8<------------------------------------


Thank you.
metro2003Asked:
Who is Participating?
 
jrb1Commented:
Well, I'd push the fclose to after the cursor close:

          IF UTL_FILE.is_open (log_handle) THEN
          UTL_FILE.fclose (log_handle);
          END IF;

Also, my example was from a unix server.  I think you said you're on Windows...if so, check for a c:\temp directory.  If it's there, run this SQL:

create or replace directory TMP as 'c:/temp';

And then retest your procedure.
0
 
slightwv (䄆 Netminder) Commented:
You can use a combination of put and put_line like:
  dbms_output.put(rec_customers.column1 ...,1,200);
  dbms_output.put(rec_customers.column1 ...,201,200);
  dbms_output.put_line(rec_customers.column1 ...,401,200);

or

use UTL_FILE (since dbms_output has an upper limit to the total amount of data it can output.  I believe it's around 1 million characters).

or

use a SQL*Plus report and forget all about the stroed procedure:

set pages 0
set lines 10000
set trimspool on
spool somefile
select ... from ...;
spool off

0
 
jrb1Commented:
I like UTL_FILE for what you're looking for.  I wrote this little program:

declare
   log_handle   UTL_FILE.file_type;
BEGIN
   log_handle := UTL_FILE.fopen ('TMP', 'test.dat', 'w');
   UTL_FILE.put_line (log_handle, 'this is a test');
   IF UTL_FILE.is_open (log_handle) THEN
      UTL_FILE.fclose (log_handle);
   END IF;
END;
/

To do this, you have to define a directory to write the file to (in my case, TMP).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jrb1Commented:
The create directory was:

create or replace directory TMP as '/tmp';
0
 
slightwv (䄆 Netminder) Commented:
Just to add:  If you use the UTL_FILE approach, the file can only be written to the DB servers file system not the clients.
0
 
metro2003Author Commented:

Thank you all for the quick response.  I do prefer the UTL_FILE. method, however, I'm a bit confused and I would like to know where would I insert UTL_FILE section this in my code? Would I need the LOOP and the FETCH part?
I guess what I'm asking is do I have to write to the file row by row or does the plsql script understand to write the entire output at once?
and are there any limitation for UTL_FILE approach?  unline DBMS_OUTPUT?

thanks you.
0
 
jrb1Commented:
You would do the fopen at the top, then you are correct....loop and fetch through your cursor.  You could write each row as you go on, and after you reach the end, close the file.

The only limitation to UTL_FILE will be from your file system.  So if the file is in Unix with a quota, that could limit you.  I don't believe there is any hard limit like dbms_output.
0
 
metro2003Author Commented:
I tried it and I can't seem to find the file on the server.  as a first step, I ran the following:

create or replace directory TMP as '/tmp';

I then modifed my code as to include the write to file procedure.
am I doing anything wrong?
it looks as follows:

-------------------------------- 8< --------------------------------------
CREATE OR REPLACE PROCEDURE GET_CUSTOMERS
     ( p_month         IN   varchar2     )
AS    
   CURSOR p_customer_cursor is  
SELECT ...
FROM ...
WHERE ...
Group By ...
Order by ...

 rec_customers    p_customer_cursor%ROWTYPE;
 log_handle   UTL_FILE.file_type;

BEGIN

DBMS_OUTPUT.ENABLE (1000000);
log_handle := UTL_FILE.fopen ('TMP', 'test.dat', 'w');

open p_customer_cursor;
     LOOP
         
         FETCH p_customer_cursor INTO rec_customers;
             EXIT WHEN p_customer_cursor%NOTFOUND;
             
        UTL_FILE.put_line(log_handle, 'this is a test');

         DBMS_OUTPUT.PUT_LINE( ... );
                                                      
            IF UTL_FILE.is_open (log_handle) THEN
            UTL_FILE.fclose (log_handle);
            END IF;
             
     END LOOP;
     CLOSE p_customer_cursor;
END GET_CUSTOMERS;
/
-------------------------------- 8< --------------------------------------

Thank you.
0
 
metro2003Author Commented:
Great !!! Thank you jrb1 !!.. it worked.  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.