Executing stored procedures in oracle inside a package using dbms_job

jobsoracleoracle-9i

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;

Best Answer

If you just want to run the job now then don't add a date/time. You can use this as a test case, run as an anonymous block to verify it works then modify your package.

DECLARE
l_jobno1 NUMBER;
BEGIN
dbms_job.submit(l_jobno1, 'SP_ABC();');
END;

You should definitely qualify the procedure name with the schema owner as in XYZ.sp_ABC()

Related Question