Executing stored procedures in oracle inside a package using dbms_job

Question

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;

Answer 1

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;');