0

I am trying to execute multiple stored procedures through package using DBMS_JOB. Even if the job is getting submitted and is showing in USER_JOBS its not getting started for some reason. I have tried with and without putting the 'START DATE' parameter with the same result i.e. no start.

I am using ORACLE 9i and i guess i cannot use dbms_scheduler.

Find below the code :

--PACKAGE BODY--

create or replace
PACKAGE BODY PKG_TEST IS

  PROCEDURE PASSPORT_DC_1 IS

  l_jobno binary_integer;
  l_jobno1 binary_integer;

    BEGIN

       dbms_job.submit(l_jobno1, 'BEGIN SP_ABC(); END;',SYSDATE );
       DBMS_OUTPUT.PUT_LINE('SP_UPDATE_TIDCUMTL :' || l_jobno1);
       COMMIT;

       dbms_job.submit(l_jobno, 'BEGIN SP_XYZ(); END;',SYSDATE);
       DBMS_OUTPUT.PUT_LINE('SP_UPDATE_TIDPTDTL :' || l_jobno);
       COMMIT; 

  END PASSPORT_DC_1;

--PACKAGE SPEC--

create or replace PACKAGE PKG_TEST AS

  PROCEDURE PASSPORT_DC_1;

END PKG_TEST;
qwerty
  • 11
  • 3
  • Its a while since I used 9i but try setting start_date to SYSDATE + 1/1440. Check that the job definition is as you expect by looking in DBA_JOBS and that the job isn't 'broken'. Then see if you can force the job to run using DBMS_JOB.RUN with the jobid. – BriteSponge Jan 31 '17 at 09:33
  • when i try to run it manually by doing DBMS_JOB.RUN(job id). It is executing, but the purpose of parallelism is defeated as DBMS_JOB.RUN(job id) executes the jobs sequentially. in the example there are 2 jobs which must start in parallel. – qwerty Jan 31 '17 at 09:39

1 Answers1

0

the 3. parameter of the dbms_job.submit is the interval (see doc). try to omit the third parameter (SYSDATE) if you whant to run you stored procedure only once.:

dbms_job.submit(l_jobno1, 'BEGIN SP_ABC(); END;');
schurik
  • 7,798
  • 2
  • 23
  • 29
  • I have run omitting the last parameter as well. dbms_job.submit(l_jobno1, 'BEGIN SP_ABC(); END;' ); DBMS_OUTPUT.PUT_LINE('SP_ABC() :' || l_jobno1); COMMIT; still the job is not starting – qwerty Jan 31 '17 at 09:27