• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5050
  • Last Modified:

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.
0
metro2003
Asked:
metro2003
  • 4
  • 3
  • 2
1 Solution
 
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
 
jrb1Commented:
The create directory was:

create or replace directory TMP as '/tmp';
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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
 
metro2003Author Commented:
Great !!! Thank you jrb1 !!.. it worked.  :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now