Oracle DBMS_SCHEDULER sysdate argument

Question

I'm using Oracle12c. I'm working with DBMS_SCHEDULER to execute a procedure every minute. I want to pass one argument to this procedure with the current datetime (SYSDATE) in each execution.

But this argument is evaluated just one time, when I create the job. Then, in every consecutive executions, the value of the argument is always the same (the create job date).

-- procedure signature
test_proc(v_aud_date in date);

-- scheduler_job
begin
    dbms_scheduler.create_job (
          job_name              => 'test_JOB'
        , job_type              => 'STORED_PROCEDURE'
        , job_action            => 'test_proc'
        , number_of_arguments   => 1
        , start_date            => SYSTIMESTAMP
        , repeat_interval       => 'freq=minutely; interval=1;'
    );

    dbms_scheduler.set_job_anydata_value(
          job_name          => 'test_JOB'
        , argument_position => 1
        , argument_value    => sys.anydata.convertDate(sysdate)
    );

    dbms_scheduler.enable('test_JOB');
end;

I want to get the current SYSDATE for every execution. I can't change the procedure. Is a black box for me.

Is it possible with DBMS_SCHEDULER?

P.S.: Probably I've made mistakes... Sorry for my English!

I guess this could help [Passing arguments to oracle stored procedure through scheduler job](http://stackoverflow.com/questions/9179775/passing-arguments-to-oracle-stored-procedure-through-scheduler-job) — Leo, Dec 15 '16 at 09:11

score 1 · Answer 1 · answered Dec 15 '16 at 09:11

1

To pass the argument to the scheduler you can use as below:

, job_action            => 'test_proc(v_aud_date); END;'

answered Dec 15 '16 at 09:11

XING

9,608
4
22
38

Boneist · Accepted Answer · 2016-12-15T14:23:26.177

You could create the job as a PL/SQL block, like so:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
     job_name             => 'test_JOB',
     job_type             => 'PLSQL_BLOCK',
     job_action           => 'BEGIN test_proc(sysdate); END;',
     start_date           => SYSTIMESTAMP,
     repeat_interval      => 'freq=minutely; interval=1';
     enabled              =>  TRUE,
     comments             => 'Test job');
END;
/

score 0 · Answer 3 · answered Dec 15 '16 at 09:08

sysdate is evaluated on execution time. Script that creates job you call once so this date is evaluated once when created job. If you need sysdate from execution not creation time just use sysdate inside code that you are executing not pass it via parameter. In every place of PL/SQL code you have sysdateso in test_proc add declare curr_time date := sysdate; and use curr_time in place where you would use previously passed parameter.
You can also change procedure to have this parameter default sysdate and in job call without parameter at all.

Oracle DBMS_SCHEDULER sysdate argument

3 Answers3