RE: Create Job Example

If you do, we have a job wizard, it helps you setup the procedure via GUI but also gives you the PL/SQL block.

The number_of_arguments bit and then the set_job_argument_value() calls are what you’re interested in I believe.

Jeff

BEGIN     DBMS_SCHEDULER.CREATE_JOB (             job_name => '"SQLDEVDEMO"."CALLS_SP"',

            job_type => 'STORED_PROCEDURE',

            job_action => 'SQLDEVDEMO.UNTAPPD_PROC',

            number_of_arguments => 1,

            start_date => NULL,

            repeat_interval => NULL,

            end_date => NULL,

            enabled => FALSE,

            auto_drop => FALSE,

            comments => 'calls an sp for example');

    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(              job_name => '"SQLDEVDEMO"."CALLS_SP"',

             argument_position => 1,

             argument_value => '5');

    DBMS_SCHEDULER.SET_ATTRIBUTE(              name => '"SQLDEVDEMO"."CALLS_SP"',

             attribute => 'store_output', value => TRUE);

    DBMS_SCHEDULER.SET_ATTRIBUTE(              name => '"SQLDEVDEMO"."CALLS_SP"',

             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);

    DBMS_SCHEDULER.enable(

             name => '"SQLDEVDEMO"."CALLS_SP"');

END;  

From: Woody McKay [mailto:woody.mckay_at_gmail.com] Sent: Wednesday, November 11, 2015 10:53 AM To: ORACLE-L
Subject: Create Job Example

Hi,

Does anyone have a quick example of how to create a job that calls a procedure and passes 4 parameters to that procedure?

The docs are confusing to me regarding this and haven't found a good example yet. I see the NUMBER_OF_ARGUMENTS parameter, but don't see where values can be passed to the procedure in the job. 

This is for Apex 5 on 12.1.0.2.  From the Apex page (on button click), we need to call a procedure that maintains users.  Someone had said this would best be done via a job. I appears that the procedure can not be called directly from Apex due to needed security privs.

--

Sincerely,

Woody

--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 11 2015 - 17:03:20 CET