I have created the next program and subsequent Oracle JOB:
BEGIN
DBMS_SCHEDULER.create_program (program_name => 'myProg',
program_action => 'myProc',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 3,
enabled => FALSE);
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name => 'myProg',
argument_position => 1,
argument_type => 'NUMBER');
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name => 'myProg',
argument_position => 2,
argument_type => 'NUMBER');
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name => 'myProg',
argument_position => 3,
argument_type => 'NUMBER',
DEFAULT_VALUE => NULL);
DBMS_SCHEDULER.create_job ('myJob',
program_name => 'myProg',
enabled => FALSE,
comments => 'Send data');
DBMS_SCHEDULER.SET_ATTRIBUTE ('myJob', 'PARALLEL_INSTANCES', TRUE);
DBMS_SCHEDULER.SET_ATTRIBUTE ('myJob',
'logging_level',
DBMS_SCHEDULER.LOGGING_FULL);
END;
/
Now, I have a user who can run/execute jobs that calls the next procedure:
PROCEDURE runJOB(param1 IN PLS_INTEGER,
param2 IN PLS_INTEGER DEFAULT NULL,
param3 IN PLS_INTEGER DEFAULT NULL)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DBMS_SCHEDULER.enable ('myProg');
DBMS_SCHEDULER.set_job_argument_value ('myJob', 1, TO_CHAR (param1));
DBMS_SCHEDULER.set_job_argument_value ('myJob', 2, TO_CHAR (param2));
DBMS_SCHEDULER.set_job_argument_value ('myJob', 3, TO_CHAR (param3));
--DBMS_SCHEDULER.enable ('myJob');
DBMS_SCHEDULER.RUN_JOB (JOB_NAME => 'myJob', USE_CURRENT_SESSION => FALSE);
--DBMS_SCHEDULER.disable ('myJob');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END runJOB;
What are my problems here?
enable
or a run_job
using the USE_CURRENT_SESSION
parameter
FALSE
. I think this works.runJOB
procedure. The job can run in 20 seconds. In this 20
seconds, user B can call the same procedure, in a different session.
That's why I've tried to use the PARALLEL_INSTANCES
attribute, but
I get only one execution. I think Oracle sees that the job is
running, so discard the second attempt to run.In resuming, I need a job that must be executed in async mode and with multiple instances at the same time.
After a "double" execution of the job for two instances I only get one record in user_SCHEDULER_JOB_RUN_DETAILS
table, but 2 enable job's for two different users (SGSS and EX01882_BD)
52367532 26/12/2016 12:08:44,584878 +00:00 SGSS myJob DEFAULT_JOB_CLASS RUN SUCCEEDED (HugeClob)
52364238 26/12/2016 12:08:36,529539 +00:00 SGSS myJob DEFAULT_JOB_CLASS ENABLE EX01882_BD (HUGECLOB)
52367534 26/12/2016 12:08:34,302807 +00:00 SGSS myJob DEFAULT_JOB_CLASS ENABLE SGSS (HUGECLOB)
Any help?
Note: I cannot have job's different names as in this solution (How run two or more instances of an oracle job in the same time?), because the job is already created and the users who call this job don't have permissions to create.
DBMS_SCHEDULER.RUN_JOB (JOB_NAME => 'myJob', USE_CURRENT_SESSION => FALSE);
now, check documentation:
This specifies whether or not the job run should occur in the same session that the procedure was invoked from.
When use_current_session is set to TRUE:
The job runs as the user who called RUN_JOB, or in the case of a local external job with a credential, the user named in the
credential.You can test a job and see any possible errors on the command line.
run_count, last_start_date, last_run_duration, and failure_count are not updated.
RUN_JOB can be run in parallel with a regularly scheduled job run.
When use_current_session is set to FALSE:
The job runs as the user who is the job owner.
You need to check the job log to find error information.
run_count, last_start_date, last_run_duration, and failure_count are updated.
RUN_JOB fails if a regularly scheduled job is running.
Solution: Event-Based Jobs
GRANT AQ_ADMINISTRATOR_ROLE TO USERA;
GRANT EXECUTE ON DBMS_SCHEDULER TO USERA;
GRANT EXECUTE ON sys.DBMS_SCHEDULER TO USERA;
GRANT EXECUTE ON sys.DBMS_ISCHED TO USERA;
GRANT CREATE JOB TO USERA;
GRANT CREATE EXTERNAL JOB TO USERA;
CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
event_name VARCHAR2(30)
);
BEGIN
-- Create a queue table to hold the event queue.
DBMS_AQADM.create_queue_table (
queue_table => 'event_queue_tab',
queue_payload_type => 't_event_queue_payload',
multiple_consumers => TRUE,
comment => 'Comments');
--Create the event queue.
DBMS_AQADM.create_queue (queue_name => 'event_queue',
queue_table => 'event_queue_tab');
-- Start the event queue.
DBMS_AQADM.start_queue (queue_name => 'event_queue');
END;
/
BEGIN
DBMS_SCHEDULER.create_program (
program_name => 'myProg',
program_action => 'USERA.PACKAGE.myProc',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 3,
enabled => FALSE);
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name => 'myProg',
argument_position => 1,
argument_type => 'NUMBER');
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name => 'myProg',
argument_position => 2,
argument_type => 'NUMBER');
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (program_name => 'myProg',
argument_position => 3,
argument_type => 'NUMBER',
DEFAULT_VALUE => NULL);
DBMS_SCHEDULER.enable ('myProg');
DBMS_SCHEDULER.create_job (
'myJob',
program_name => 'myProg',
comments => 'StartJob',
auto_drop => FALSE,
start_date => SYSTIMESTAMP,
event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
queue_spec => 'event_queue',
enabled => FALSE);
COMMIT;
END;
/
PROCEDURE enqueue(param1 IN PLS_INTEGER,
param2 IN PLS_INTEGER DEFAULT NULL,
param3 IN PLS_INTEGER DEFAULT NULL)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
l_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
l_message_handle RAW (16);
l_queue_msg t_event_queue_payload;
BEGIN
l_queue_msg := t_event_queue_payload ('give_me_a_prod');
-- Define arguments
DBMS_SCHEDULER.set_job_argument_value ('myJob', 1, TO_CHAR (param1));
DBMS_SCHEDULER.set_job_argument_value ('myJob', 2, TO_CHAR (param2));
DBMS_SCHEDULER.set_job_argument_value ('myJob', 3, TO_CHAR (param3));
DBMS_AQ.enqueue (queue_name => 'USERA.event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
SQLERRM || ' - ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END enqueue;
In Package:
PROCEDURE myProc(param1 IN PLS_INTEGER,
param2 IN PLS_INTEGER DEFAULT NULL,
param3 IN PLS_INTEGER DEFAULT NULL)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
...
PKG_SAP_FSCD_INTERRECIBOS.callSomething....
...
END myProc;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With