scheduled job on procedure with DATE parameters

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.
scheduler_date_1

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.

Advertisements

2 thoughts on “scheduled job on procedure with DATE parameters

  1. 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;
      /
      

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s