Does PL/SQL Have an Equivalent for Spool ? 2

[JohnBates]

hi Oracle experts,

This may be the wrong forum for this question; if so, please let me know.

This is my crummy little procedure in a 10g database:
It was originally a command line script.

Notice the Spool CleanDB11.log line is inactivated. Apparently the Spool keyword is for SQL Plus and cannot be used in a procedure - is that correct?

Is there an equivalent keyword for Spool in PL/SQL ?

Thanks. John

--Spool CleanDB11.log


UPDATE AFTSCHINF SET DELETED_ON = sysdate where DELIND='Y' AND DELETED_ON is null;


DELETE FROM AFTSCHINF WHERE delind = 'Y' AND DELETED_ON < (SYSDATE -7);


COMMIT;

 

[dbtoo2001]

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#ARPLS069

 

[Dagon]

Spool CleanDB11.log line is inactivated. Apparently the Spool keyword is for SQL Plus and cannot be used in a procedure - is that correct?

That's correct. You can however, put, it before the start of the procedure and any output produced by the procedure will be written to the log file. Therefore, you could use dbms_output statements (with a "set serveroutput on") to produce some output from your procedure e.g.

spool test.log
set serveroutput on size 1000000

begin
   dbms_output.put_line('results');
end;
/

spool off

 

[JohnBates]

Dagon, that'w more like what I had hoped for - a workaround.

I may have questions about implementing your idea later.

Thanks. John

 

[Dagon]

It's not really a workaround - this is exactly how the software is designed to work !

 

[TheKidd]

Thanks for this info Dagon. I had been searching for a way to spool in PL/SQL.