DBMS_JOB and JOB not executing

From: Phil Melendez <pmelendez_at_gers.com>
Date: 1997/12/18
Message-ID: <01bd0bed$4d74d920$694c5ecc@pme.cts.com>#1/1

All,

I am creating a Job as follows:

        dbms_job(:v_jobnum, 'bt_tsale.cr_sale(''1'');', sysdate, 'NULL' );

I want the job to only execute once. It is showing in the user_job view as follows:

SQLWKS> select job, last_date, last_sec, this_date, this_sec, next_date, next_sec,

     2> substr(interval,1,20) int, broken, failures, substr(what,1,30) what

     3> from user_jobs;
JOB LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC INT B WHAT                        

• --------- -------- --------- -------- --------- -------- ---- -

  ---

  26 18-DEC-97 10:51:37 NULL N bt_tsale.cr_sale('1'); 1 row selected.

It never gets executed by the SNP processes which are set as follows:

#How many job processes to start

	job_queue_processes = 5


	#Wake up job process every five minutes

	job_queue_interval = 300             

I can execute the job manually as follows and it will run properly and it will
be remove itself from the job queue.

SQLWKS> execute dbms_job.run('26');
Statement processed.
SQLWKS> select job, last_date, last_sec, this_date, this_sec, next_date, next_sec,

     2> substr(interval,1,20) int, broken, failures, substr(what,1,30) what

     3> from user_jobs;
JOB        LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC INT    
             B FAILURES   WHAT                          

---------- --------- -------- --------- -------- --------- --------

-------------------- - ---------- ------------------------------

0 rows selected.

I am stumped on this one. I suspect it may have todo with setting the job interval
parameter to dbms_job.submit to 'NULL', but the documentation says to set it to null
if you want the job to only execute once.

OS: dgux cosmos R4.20 generic AViiON PentiumPro Oracle7 Server Release 7.3.3.3.0 - Production Release With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.3.3.0 - Production

Regards,

Phil M. Received on Thu Dec 18 1997 - 00:00:00 CST