Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Primitive dbms_output.submit question ...
"D. Alvarado" <laredotornado_at_zipmail.com> wrote in message
news:9fe1f2ad.0204171302.358fe0e9_at_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.
Add 'commit' at an appropiate place in your anonymous block and start blushing.
Hth
-- Sybrand Bakker Senior Oracle DBA to reply remove '-verwijderdit' from my e-mail addressReceived on Wed Apr 17 2002 - 18:05:36 CDT