Step-1:--===At first create the SCHEDULE Program ===
BEGIN
DBMS_SCHEDULER.DROP_PROGRAM
(program_name => 'EDWTEST.PMASTER_PROCEDURE_PROG');
END;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'EDWTEST.PMASTER_PROCEDURE_PROG'
,program_type => 'PLSQL_BLOCK'
,program_action => 'BEGIN EDWTEST.PMASTER_PROCEDURE(trunc(sysdate)); END;'
,number_of_arguments => 0
,enabled => FALSE
,comments => 'MIS data'
);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'EDWTEST.PMASTER_PROCEDURE_PROG');
END;
/
Step-2:--=====create the SCHEDULE Job ===
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'EDWTEST.PMASTER_PROCEDURE_1'
,start_date => TO_TIMESTAMP_TZ('2018/11/28 23:30:00.000000 Asia/Dacca','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => 'freq=daily;byhour=23;byminute=30;bysecond=0'
,end_date => NULL
,program_name => 'EDWTEST.PMASTER_PROCEDURE_PROG'
,comments => 'MIS DATA Transfer'
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'EDWTEST.PMASTER_PROCEDURE_1'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'EDWTEST.PMASTER_PROCEDURE_1'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'EDWTEST.PMASTER_PROCEDURE_1'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'EDWTEST.PMASTER_PROCEDURE_1'
,attribute => 'MAX_RUNS');
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'EDWTEST.PMASTER_PROCEDURE_1'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
EXCEPTION
-- could fail if program is of type EXECUTABLE...
WHEN OTHERS THEN
NULL;
END;
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'EDWTEST.PMASTER_PROCEDURE_1'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'EDWTEST.PMASTER_PROCEDURE_1'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'EDWTEST.PMASTER_PROCEDURE_1'
,attribute => 'AUTO_DROP'
,value => FALSE);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'EDWTEST.PMASTER_PROCEDURE_1');
END;
