Scheduled Job Call Package with IN-Parameter
791888Dec 6 2010 — edited Dec 6 2010Hi,
I'd like to create a job using the scheduler. The procedure to be called is declared in a package and gives values thru input -parameter.
Here is my code:
DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'max_req_del_prg'
,program_type => 'STORED_PROCEDURE'
,program_action => 'MAXIMA_REQUEST_PKG.max_req_delete_proc'
,number_of_arguments => 1
,enabled => FALSE
,comments => 'Job defined to delete requests 2 years older than current date.'
);
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT
(
program_name => 'max_req_del_prg'
,argument_position => 1
,argument_name => 'roll_out_period'
,argument_type => 'INTEGER'
,default_value => 'default'
);
DBMS_SCHEDULER.ENABLE(NAME => 'max_req_del_prg');
DBMS_SCHEDULER.CREATE_SCHEDULE
(
schedule_name => 'max_req_del_sch'
,start_date => SYSTIMESTAMP
,repeat_interval => 'FREQ=MINUTELY'
,comments => 'Minutely' --Weekly at 22:00'
);
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'max_req_del_job'
,program_name =>'max_req_del_prg'
,schedule_name =>'max_req_del_sch'
,enabled => FALSE
,comments => 'Minutely' --'Run demo program every week at 22:00'
);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
(
job_name => 'max_req_del_job'
,argument_position => 1
,argument_value => 'roll_out_period'
);
dbms_scheduler.ENABLE('max_req_del_job');
COMMIT;
Do I need to use program and schedule here along with Job or just calling job is enough.
what is wrogn in above data. I am not able to run the job. No error shown sometimes, and sometimes show as prog, sche, job already exists even if they do not exists.