Multiple spool definitions within stored procedure

[jerryreed]

I'm curious if it is possible to have multiple Spool outputs within an Oracle Stored Procedure.

Basically, if a record hits a certain condition,in this case if it is located within the second loop, write it to one file. Else write it to another.

CODE example (receiving errors when assigning spool after BEGIN statement):
for r1 in c1 LOOP
v_found := 'N';

FOR r2 in c2(r1.name) LOOP
v_found := 'Y';
set spool on
spool \\location\OnTable.txt
dbms_output.put_line(r1.name || ' IS ON TABLE');
set spool off
END LOOP;

if v_found = 'N' then
spool \\location\NotOnTable.txt
dbms_output.put_line(r1.name || ' IS NOT ON TABLE');
end if;

END LOOP;

thanks,
Jerry

 

[SantaMufasa]

Jerry,

I believe we can achieve your objectives in a satisfactory fashion. Instead of using SQL*Plus's SPOOL feature (which stores screen output to your client machine), you can use the Oracle PL/SQL stored procedure UTL_FILE. This procedure lets you open simultaneously virtually as many flat files as you wish, for WRITE, READ, or APPEND. This procedure writes to a server-side file system (versus client-side file system).

Let me know if you need instruction on how to use UTL_FILE.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:32 (13Jul04) UTC (aka "GMT" and "Zulu"), 12:32 (13Jul04) Mountain Time)

 

[jerryreed]

thanks so much for the prompt reply. Doesn't a system admin or DBA need to create/and or set something within an Oracle folder to use this Utility (UTL_FILE) correctly? I started with this in mind, however, I continually received the below error message (the c: is a valid path). I've pasted a short test below with the error I receive when attempting to use UTL_FILE.
thanks!


1 declare
2 wfile_handle UTL_FILE.FILE_TYPE;
3 wstring varchar2 (1000);
4 BEGIN
5 wstring := 'THIS IS A TEST.';
6 /* Open the file for write */
7 wfile_handle := UTL_FILE.FOPEN ('c:' , 'UTL_FILE_TEST.txt', 'w');
8 /* write the text to file */
9 utl_file.put_line (wfile_handle, wstring);
10 /* Close the file */
11 UTL_FILE.FCLOSE (wfile_handle);
12 EXCEPTION
13 WHEN UTL_FILE.INVALID_PATH then
14 dbms_output.put_line ('Please verify the directory / file name');
15 RAISE;
16 WHEN OTHERS then
17 RAISE;
18* end;
19 /
Please verify the directory / file name
declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at line 15

 

[SantaMufasa]

Jerry,

Yes, your DBA must set "utl_file_dir = *" (or some specific directory in place of the "*"), in the instance parameter file. To check the current value, in SQL*Plus on Oracle 9, you issue the command, "show parameter utl". The result should be "*" or a specific path. If the value is null, then your DBA must correctly set the value.

Remember that the utl_file_dir will be on the server, as well. Is your instance on a Windows machine? That's the only way you could ever successfully write to "C:" using UTL_FILE_DIR.

These are the two issues you must confirm to resolve your problem.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:01 (13Jul04) UTC (aka "GMT" and "Zulu"), 14:01 (13Jul04) Mountain Time)

 

[jaggiebunnet]

If you are using Oracle 9iR2 you don't need to set the UTL_FILE_DIR you can just set it up using directory objects see

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/u_file.htm#998101

 

[jerryreed]

thanks SantaMufasa & jaggiebunnet -- I'll keep after it with your suggestions. Tek-Tips always has great feedback.
-Jerry