Do you use SQL Developer?
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@xxxxxxxxx]
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