Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Scheduled Job Call Package with IN-Parameter

791888Dec 6 2010 — edited Dec 6 2010
Hi,
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 3 2011
Added on Dec 6 2010
1 comment
406 views