Re: Urgent! on dbms_job

Henk Boen schrieb:

> Hello all,
> The configuration: oracle 8.1.5 on NT4 or NT2000
> the following parameters are set in init.ora
> job_queue_interval = 20 (20 seconds?)
> job_queue_processes = 1
>
> The problem is that I am not able to run dbms_job.submit
> Even with a simple tes tit does not work:
> I created the following table:
> testh (name varchar2(20))
> insert into testh values('xxxx');
>
> and procedure:
> create or replace procedure putname(p_name in varchar2)
> as
> begin
> update testh
> set name=p_name;
> end;
> /
>
> in sqlplus
> var x number;
>
> (as a dba !)
> sql>exec dbms_job.submit(:x,'putname(''helpppppp'') ; commit ; ',
>

The commit here is not necessary, since the "session" should exit gracefully and no exception occurs and thus implicitly commits

> sysdate+1/1440);

I just dont see a COMMIT for the DBMS_JOB.SUBMIT here. Yes, Oracle stupidly wants a COMMIT even it is not obvious as no DML statement is issued directly. But that is how Oracle wants it. I have been caught by this myself.

>
> plssql succesfully completed
>
> Just wait and wait and wait....
> select job from user_jobs just gave the same jobnr. as the value of x
> (print x)
> select * from testh just give 'xxxx'....
>
> Only when i do dbms.run(:x) it will update the table.
>
> I have looked in bdump but no trace file of snp0.trc of something alike.
>
> I have also look in the alertlog and also no indication
>

The best way to check if your job has been really submitted is to do a SELECT * FROM USER_JOBS from _another_ session (due to the COMMIT consideration)

Hth

>
> What did I do wrong?????? Do I missed some points here?
>
> Glad to hear if somebody else has encounters the same problem or has a
> solution for me.
>
> henk
>
> PS: This my second post for the same problem

--
To reply to me via email, please substitute the text no_spam with mailbox in
the return address.