Home » SQL & PL/SQL » SQL & PL/SQL » spool output to a file in dbms_job.submit (oracle 10.2)
spool output to a file in dbms_job.submit [message #405892] Sun, 31 May 2009 04:29 Go to next message
ridhi_sundar
Messages: 184
Registered: November 2007
Location: Bangalore
Senior Member
Hi

I am using dbms_job.submit() to schedule a procedureusing the below statements.
begin
sys.dbms_job.submit(job => v_job, what=>'execute_load();', 
next_date =>to_date('23-04-2009 09:55:00','DD-MM-YYYY HH:MI:SS'), interval=> 'sysdate+ 1');
end;

I want to get the output spooled to a log file every time when the job is being executed. So that I can find out if any error happened during the execution of the job.

My procedure is like:
Create or replace procedure execute_load is

v_SourceName VARCHAR2(15) :='ORACLE'; 

BEGIN
Stage_Create_pkg.InsertStageData(v_SourceName);
..
..
end execute_load;

Please suggest what changes I need to do in the procedure to get the output spooled to the logfile. Or if there is some other way to generate the log file.

Thanks in advance

Ridhi Sundar

[Updated on: Sun, 31 May 2009 09:26] by Moderator

Report message to a moderator

Re: spool output to a file in dbms_job.submit [message #405899 is a reply to message #405892] Sun, 31 May 2009 09:30 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So that I can find out if any error happened during the execution of the job.

If the job failed you get a message in alert.log.

Quote:
My procedure is like:

Your procedure is like: "Ir calls another procedure". Completly useless.

Quote:
to get the output spooled to the logfile. Or if there is some other way to generate the log file.

Use utl_file to write into a file.

Regards
Michel
Previous Topic: Need to display all column's name and value of a table in a query
Next Topic: Where like variable for varchar2
Goto Forum:
  


Current Time: Mon Dec 05 21:14:10 CST 2016

Total time taken to generate the page: 0.12110 seconds