Re: does dbms_job.submit work?

A copy of this was sent to BM Lam <1116-530_at_online.de> (if that email address didn't require changing) On Wed, 30 Jun 1999 23:31:32 +0200, you wrote:

>I wrote a procedure as follows:
>
>create or replace procedure guguck as
>begin
> insert into debug_log
> (text, timestamp)
> values
> 'this record is inserted by guguck', sysdate;
> commit;
>end;
>
>
>The procedure gets compiled just fine (I have created table debug_log
>prior to that). Then
>I entered:
>
>declare
> v_JobNo binary_integer;
>begin
> dbms_job.submit(v_jobNo, 'guguck;', sysdate+1/(24*60));
>end;
>
>
>The message "PL/SQL procedure successfully completed." is returned.
>Also user_jobs showed a new job scheduled for the next minute.
>
>But when I checked debug_log after the scheduled time (several times) I
>never found the record I mean to insert in procedure GUGUCK.
>
>I tested this on Oracle7 and 8. Neither worked.
>
>Any hint/explanation will be greatly appreciated.

1- make sure you COMMIT after you dbms_job submit else *YOU* can see the job but no one else can (until you commit).

2- make sure you've set the requisite job_* init.ora parameters. for example:

SVRMGR> connect internal
Connected.
SVRMGR> show parameter job

NAME                                TYPE    VALUE
----------------------------------- ------- ------------------------------
job_queue_interval                  integer 60                            
job_queue_processes                 integer 3 

shows I have 3 job_queues (can do 3 things at a time in the background) and the queues will be inspected for new jobs every 60 seconds.   

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 02 1999 - 06:36:38 CDT