Can't get Oracle job to start running.

From: RastM <tkupp_at_pacifier.com>
Date: 1997/09/20
Message-ID: <342332d3.0@news.pacifier.com>#1/1

SCO OpenServer 5.0.2
Oracle Workgroup Server 7.3.2.2.0

I cannot get a PL/SQL procedure to run as a job. It just sits there in

USER_JOBS with a NEXT_DATE of the time it was supposed to have run, and no
LAST_DATE but it will not start executing.  I can force it to run with the
DBMS_JOB.RUN procedure, but otherwise, it will not start up by itself. 

The job never shows up in DBA_JOBS and, of course, DBA_JOBS_RUNNING.

I set the JOB* parameters in init.ora as

	job_queue_processes = 1
	job_queue_interval = 60

The *SNP* process appears to be running.

The procecure I am trying to call has been declared as

	CREATE OR REPLACE                                                  
	PROCEDURE sleeptwo AS                                              
	        start_date      DATE;                                      
        	i               NUMBER;                                    
	BEGIN                                                              
        	INSERT INTO timings_tmp VALUES ('sleeptwo START',  sysdate);
                                                                   
	        start_date := sysdate;                                     
                                                                   
        	WHILE sysdate - (30/86400) < start_date                    
	        LOOP                                                       
        	        i := i + 1;                                        
	        END LOOP;                                                  
                                                                   
        	INSERT INTO timings_tmp VALUES ('sleeptwo END', sysdate);  
	END sleeptwo;                                                      

This works just fine if executed with         

        BEGIN sleeptwo; END;

but if I try submitting it as a job with

	VARIABLE jobno number;
	begin
	    DBMS_JOB.SUBMIT (:jobno, 'sleeptwo;', SYSDATE + (60/86400), NULL);
	end;

it doesn't want to start up.

Any suggestions?

-- 
tkupp at pacifier dot com

Received on Sat Sep 20 1997 - 00:00:00 CDT