Hi everyone,
I'm facing this problem.
I'm coding a package in 11.2.0.2 which uses a dbms_scheduler external procedure.
This external procedure is called 2 times, but the second call must waits until the first call succeeded.
Also, if one of the external procedures fails with a stderr signal (such file not found, operation not permitted, etc.. [ORA-27369]) I want to raise an application error in order to abort the package execution.
To do so I've used a dbms_scheduler chain in order to sync the external procedure calls with two scheduler programs.
This worked like a glove, except that if the external procedure fails, in any call, the package execution continues instead of aborting with an application error like its normal behaviour
Here is my example:
conn scott/tiger
-- ** creating the external job **
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'SCOTT.MY_EXTPROC',
job_type => 'EXECUTABLE',
job_action => 'myscript.sh',
number_of_arguments => 1 );
DBMS_SCHEDULER.create_credential(
credential_name => 'SCOTT.MY_CREDENTIAL',
username => 'osuser',
password => /*pass*/);
DBMS_SCHEDULER.set_attribute('SCOTT.MY_EXTPROC', 'credential_name', 'SCOTT.MY_CREDENTIAL);
END;
/
-- *** creating the scheduler programs
begin
DBMS_SCHEDULER.create_program (
program_name => 'TEST_PROGRAM_FIRST_CALL',
program_type => 'PLSQL_BLOCK',
program_action => 'DECLARE
my_var varchar(20);
BEGIN
my_var = ''testarg-A.txt''
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => ''SCOTT.MY_EXTPROC'',
argument_position => 1,
argument_value => my_var
);
DBMS_SCHEDULER.RUN_JOB(
job_name => ''SCOTT.MY_EXTPROC'',
use_current_session => TRUE
);
END;',
enabled => TRUE,
comments => 'Programa de pruebas para ejecutar job externo');
DBMS_SCHEDULER.create_program (
program_name => 'TEST_PROGRAM_SECOND_CALL',
program_type => 'PLSQL_BLOCK',
program_action => 'DECLARE
my_var varchar(20);
BEGIN
my_var = ''testarg-B.txt''
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => ''SCOTT.MY_EXTPROC'',
argument_position => 1,
argument_value => my_var
);
DBMS_SCHEDULER.RUN_JOB(
job_name => ''SCOTT.MY_EXTPROC'',
use_current_session => TRUE
);
END;',
enabled => TRUE,
comments => 'Programa 2 de pruebas para ejecutar job externo');
end;
/
--*** creating the chain, steps and rules
BEGIN
DBMS_SCHEDULER.create_chain (
chain_name => 'test_chain_1',
rule_set_name => NULL,
evaluation_interval => NULL,
comments => 'A test chain.');
DBMS_SCHEDULER.define_chain_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_1',
program_name => 'TEST_PROGRAM_FIRST_CALL');
DBMS_SCHEDULER.define_chain_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_2',
program_name => 'TEST_PROGRAM_SECOND_CALL');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'TRUE',
action => 'START "CHAIN_STEP_1"',
rule_name => 'chain_rule_1',
comments => 'First link in the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => '"CHAIN_STEP_1" SUCCEEDED',
action => 'START "CHAIN_STEP_2"',
rule_name => 'chain_rule_2',
comments => 'Second link in the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => '"CHAIN_STEP_2" SUCCEEDED',
action => 'END',
rule_name => 'chain_rule_3',
comments => 'End of the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => '"CHAIN_STEP_1" FAILED',
action => 'END',
rule_name => 'chain_rule_4',
comments => 'End of the chain if step_1 fails');
END;
/
Checking
SELECT owner,
chain_name,
step_name,
program_owner,
program_name,
step_type
FROM dba_scheduler_chain_steps
ORDER BY owner, chain_name, step_name;
OWNER CHAIN_NAME STEP_NAME PROGRAM_OWNER PROGRAM_NAME STEP_TYPE
SCOTT TEST_CHAIN_1 CHAIN_STEP_1 SCOTT TEST_PROGRAMA_FIRST_CALL PROGRAM
SCOTT TEST_CHAIN_1 CHAIN_STEP_2 SCOTT TEST_PROGRAMA_SECOND_CALL PROGRAM
SELECT owner,
chain_name,
rule_owner,
rule_name,
condition,
action,
comments
FROM dba_scheduler_chain_rules
ORDER BY owner, chain_name, rule_owner, rule_name;
OWNER CHAIN_NAME RULE_OWNER RULE_NAME CONDITION ACTION COMMENTS
SCOTT TEST_CHAIN_1 SCOTT CHAIN_RULE_1 TRUE START "CHAIN_STEP_1" First link in the chain.
SCOTT TEST_CHAIN_1 SCOTT CHAIN_RULE_2 "CHAIN_STEP_1" SUCCEEDED START "CHAIN_STEP_2" Second link in the chain.
SCOTT TEST_CHAIN_1 SCOTT CHAIN_RULE_3 "CHAIN_STEP_2" SUCCEEDED END End of the chain.
SCOTT TEST_CHAIN_1 SCOTT CHAIN_RULE_4 "CHAIN_STEP_1" FAILED END End of the chain if step_1 fails
--*** enabling chain
BEGIN
DBMS_SCHEDULER.enable ('test_chain_1');
END;
/
Now I'll provoque an external procedure error and it should fail due to an "Operation not permited" error (I've deleted the argument file "testarg-A.txt", so the unix script "myscript.sh" returns a "No such file or directory" error).
Then when I manually run the chain the application will
not abort.
BEGIN
DBMS_SCHEDULER.run_chain (
chain_name => 'test_chain_1',
job_name => 'test_chain_1_run_job',
start_steps => null);
dbms_output.put_line('post chain');
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Error en el proceso de copia - '||SQLCODE||': '||SQLERRM);
END;
/
Procedimiento PL/SQL terminado correctamente.
But if I run the external procedure manually then the aplication aborts with an "ORA-27369 job of type EXECUTABLE failed with exit code: xxx".
This is
the expected and desired behaviour.
BEGIN
DBMS_SCHEDULER.RUN_JOB(
job_name => 'SCOTT.MY_EXTPROC',
use_current_session => TRUE);
DBMS_OUTPUT.PUT_LINE('post job OK');
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Error en el proceso de copia - '||SQLCODE||': '||SQLERRM);
END;
/
ERROR en línea 1:
ORA-20001: Error en el proceso de copia - -27369: ORA-27369: fallo del trabajo
de tipo EXECUTABLE con el código de salida: Operation not permitted
How can I handle the "raise_application_error" events in order to abort the application if any of the calls to the external procedure fails, and sync their execution with a scheduler chain ?
Regards