Search code examples
oracle-databasesyntaxscheduler

What is the syntax for adding parameters to the called procedure in Oracle scheduler?

So say I have 2 procedures: MYPROC1 & MYPROC2(A_PARAM INTEGER)

this works:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name => 'TEST_SCHEDULER',
        job_type => 'STORED_PROCEDURE',
        job_action => 'developer.MYPROC1', <<<<<<<<<<<<<<<
        start_date => TIMESTAMP'2011-12-4 10:30:00',
                repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
        end_date => TIMESTAMP'2011-12-4 10:45:00',
        auto_drop => FALSE,
        comments => 'TEST 1');
END;

replacing line 5 with:

job_action => 'developer.MYPROC1(2)' makes it not work. Error: ..invalid name for a database object...

So how do I call from a scheduler a parametrized procedure? Whats the syntax?

Solution

  • use job_type => 'PLSQL_BLOCK', job_action => 'BEGIN developer.MYPROC1(2); END;' instead.