Re: Primitive dbms_output.submit question ...

In article <9fe1f2ad.0204171302.358fe0e9_at_posting.google.com>, laredotornado_at_zipmail.com says...
>
>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.

did you:

1. commit -- the job is not visible to the job queue processes until you COMMIT
2. set the job_queue_processes init.ora setting to something > 0. jobs will not run if there are no job_queue_processes
3. set the job_queue_interval to something less then 1 day in length, the job queues are only inspected on that interval.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp