Primitive dbms_output.submit question ...

From: D. Alvarado <laredotornado_at_zipmail.com>
Date: 17 Apr 2002 14:02:29 -0700
Message-ID: <9fe1f2ad.0204171302.358fe0e9@posting.google.com>

Maybe I'm failing to grasp something about DBMS_OUTPUT.SUBMIT, but basically I want to run a stored procedure every hour, so I've resorted to this code:

DECLARE

	  Jobno NUMBER;
	  nx_Date DATE;
BEGIN
	-- Set the next day to run at 5:00 am on Monday.
	nx_Date := SYSDATE + 2/1440;     

	SYS.DBMS_JOB.SUBMIT(job => jobno, 
	           what => 'BEGIN my_schema.my_pkg.my_proc; END;',
	           next_date => nx_Date, 
		     interval => 'SYSDATE + 60/1440');  	        

END; Sadly, this doesn't seem to work. I know because "my_proc" updates tables and after executing this script and waiting a day, I find the tables are still empty. Oddly, when I run the command:

sqlplus> execute dbms_job.run( job_number )

the tables get properly updated. Obviously, I don't want to type "execute dbms_job.run(...)", every hour, so can I assure that my stored procedure gets run every hour. I'm using Oracle 8 if that's any help.

Thanks, Dave A. Received on Wed Apr 17 2002 - 16:02:29 CDT