To set up a scheduled job in an oracle database is really simple. Oracle SQL Developer helps a lot to do so. It has a nice configuration wizard that creates all the needed function calls to DBMS_SCHEDULER for you.
But some certain small issues can still be a hassle. For example if you want to run a procedure with parameters. Number and string parameters can be passed using DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE. If you use sql developer to create the job for you, then it will use this function for all types of parameters.
That means you can’t easily pass sysdate. Because that is simply passed as a string.
This is the code that SQL developer generates.
SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => '"MYSCHEMA"."myScheduledJob"', argument_position => 1, argument_value => '10'); SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => '"MYSCHEMA"."myScheduledJob"', argument_position => 2, argument_value => 'sysdate'); SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( job_name => '"MYSCHEMA"."myScheduledJob"', argument_position => 3, argument_value => 'false');
It is less known that you can also pass other types of parameters using DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE. It accepts an anydata data type. Anydata is like a placeholder for any scalar data type. You put a certain value or expression in and it “stores” the value and the data type.
I always change the job_name parameters from SQL Developer and replace it with a variable.
Here is an example of a real job that I set up using a date parameter value (value = “in 10 minutes”). always change the job_name parameters from SQL Developer and replace it with a variable.
-- create job
declare
v_jobname varchar2(30) := 'myScheduledJob';
v_date_format varchar2(100);
BEGIN
select value
into v_date_format
from v$nls_parameters
where parameter = 'NLS_DATE_FORMAT';
SYS.DBMS_SCHEDULER.CREATE_JOB (
job_name => v_jobname,
job_type => 'STORED_PROCEDURE',
job_action => 'MYSCHEMA.MYPACKAGE.RUN_BATCH',
number_of_arguments => 2,
start_date => TO_TIMESTAMP_TZ('2015-06-03 19:05:00 Europe/Berlin','YYYY-MM-DD HH24.MI.SS TZR'),
--start_date => systimestamp + interval '1' minute,
repeat_interval => NULL,
--end_date => TO_TIMESTAMP_TZ('2015-07-29 05:30:00 Europe/Berlin','YYYY-MM-DD HH24.MI.SS TZR'),
job_class => '"SYS"."DEFAULT_JOB_CLASS"',
enabled => FALSE,
auto_drop => FALSE,
comments => 'Do run something longrunning');
SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => v_jobname,
argument_position => 1,
argument_value => 10);
SYS.DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE(
job_name => v_jobname,
argument_position => 2,
argument_value => sys.anydata.convertDate(sysdate + interval '10' min)
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
name => v_jobname,
attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
name =>v_jobname,
attribute => 'max_run_duration', value => INTERVAL '1' DAY);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
name => v_jobname,
attribute => 'schedule_limit', value => INTERVAL '1' DAY);
SYS.DBMS_SCHEDULER.enable(
name => v_jobname);
commit;
END;
/
The expression “anydata.convertDate(sysdate + interval ’10’ min)” was used to set the parameter value.
Feel free to use that as a template for your own scheduled jobs.
I have procedure like this
PROCEDURE prepBillInfoforAccrualIntr (p_date in date);
— created a program
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(PROGRAM_NAME => ‘MY_PROGRAM’,
PROGRAM_TYPE => ‘STORED_PROCEDURE’,
PROGRAM_ACTION => ‘GENEVA_ADMIN.IPGTIBCOAPIS_LMS_GENERIC_FIXED.PREPBILLINFOFORACCRUALINTR’,
NUMBER_OF_ARGUMENTS => 1,
ENABLED => FALSE,
COMMENTS => ‘MY PROGRAM’);
END;
— defined argument
BEGIN
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(PROGRAM_NAME => ‘MY_PROGRAM’,
ARGUMENT_NAME => ‘P_DATE’,
ARGUMENT_POSITION => 1,
ARGUMENT_TYPE => ‘DATE’,
DEFAULT_VALUE => ”);
END;
— enabled
BEGIN
DBMS_SCHEDULER.ENABLE(NAME => ‘MY_PROGRAM’);
END;
— created a job
BEGIN
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => ‘MY_TEST_JOB’,
— PROGRAM_NAME => ‘MY_PROGRAM’,
JOB_TYPE => ‘STORED_PROCEDURE’,
JOB_ACTION => ‘GENEVA_ADMIN.IPGTIBCOAPIS_LMS_GENERIC_FIXED.PREPBILLINFOFORACCRUALINTR’,
START_DATE => SYSDATE,
AUTO_DROP => TRUE,
COMMENTS => ‘MY NEW JOB’);
END;
— and passing arguments
BEGIN
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOB_NAME => ‘MY_TEST_JOB’,
ARGUMENT_POSITION => 1,
ARGUMENT_VALUE => ’06-JUL-2017′);
END;
It is giving below error.
ORA-27465: invalid value 1 for attribute ARGUMENT_POSITION
ORA-06512: at “SYS.DBMS_ISCHED”, line 213
ORA-06512: at “SYS.DBMS_SCHEDULER”, line 656
ORA-06512: at line 2
View program sources of error stack?
Could you please help.
Thanks,
You can set job arguments only if you use a program. You almost have the code ready for it, but your job did not use the program.
Here is an example how to setup the program and use it for a job. Note that the job can not defined the job_type and job_action anymore, since that is done by the program.
create or replace procedure test_dbmsscheduler_use_param (p_date in date) is begin dbms_output.enable; dbms_output.put_line('Input parameter p_date = '||to_char(p_date)); end; / -- create a program for a stored procedure with a date paramter BEGIN DBMS_SCHEDULER.CREATE_PROGRAM( PROGRAM_NAME => 'TEST_PROGRAM', PROGRAM_TYPE => 'STORED_PROCEDURE', PROGRAM_ACTION => user||'.TEST_DBMSSCHEDULER_USE_PARAM', NUMBER_OF_ARGUMENTS => 1, ENABLED => FALSE, COMMENTS => 'Test program to see how DATE parameter arguments can be added' ); -- define the parameter as argument DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT( PROGRAM_NAME =>'TEST_PROGRAM', ARGUMENT_NAME => 'P_DATE', ARGUMENT_POSITION => 1, ARGUMENT_TYPE => 'DATE', DEFAULT_VALUE => null); DBMS_SCHEDULER.ENABLE(NAME => 'TEST_PROGRAM'); END; / show errors ------------------------------------------ -- create a scheduler job using the program BEGIN DBMS_SCHEDULER.DROP_JOB('TEST_JOB'); end; / BEGIN DBMS_SCHEDULER.CREATE_JOB( JOB_NAME => 'TEST_JOB', PROGRAM_NAME => 'TEST_PROGRAM', --JOB_TYPE => 'STORED_PROCEDURE', --JOB_ACTION => user||'.TEST_DBMSSCHEDULER_USE_PARAM', START_DATE => SYSDATE+interval '30' second, AUTO_DROP => false, COMMENTS => 'Test job'); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( JOB_NAME => 'TEST_JOB', ARGUMENT_POSITION => 1, ARGUMENT_VALUE => to_date('06-JUL-2017','dd-mon-rrrr') ); END; /