Sample Header Ad - 728x90

Executing stored procedures in oracle inside a package using dbms_job

0 votes
1 answer
1372 views
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;
Asked by zlatan (1 rep)
Jan 31, 2017, 06:17 AM
Last activity: Jan 31, 2017, 03:25 PM