Tuesday, August 27, 2019
How to execute a Shell Script from PLSQL? (DBMS_SCHEDULER)
In this post, we have a given
an example depicting the steps for executing the shell script from
DBMS_SCHEDULER.
Script:
BEGIN
--
Create a Job
DBMS_SCHEDULER.create_job
(job_name =>'TEST_SHELL_SCRIPT_JOB',
job_action =>'/var/tmp/TEMP/TESTSHELL.sh',
job_type =>'EXECUTABLE',
number_of_arguments=>1,
enabled =>FALSE,
auto_drop => TRUE
);
--
define the arguments and their values for each argument (in this case there is
only one)
DBMS_SCHEDULER.set_job_argument_value
(job_name =>'TEST_SHELL_SCRIPT_JOB',
argument_position => 1,
argument_value => 'SHAREORACLEAPPS'
);
--
Since we couldn't enable it when creating it with arguments, enable it now
DBMS_SCHEDULER.enable('TEST_SHELL_SCRIPT_JOB');
--
since we want this Job to execute now, we call run_job
DBMS_SCHEDULER.run_job (job_name=> 'TEST_SHELL_SCRIPT_JOB');
--
if we get here without an error, the job has completed so we can drop it
DBMS_SCHEDULER.drop_job (job_name=> 'TEST_SHELL_SCRIPT_JOB');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
END;
Testing:
Hope it helps!!
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
1 Responses to “How to execute a Shell Script from PLSQL? (DBMS_SCHEDULER)”
July 28, 2021 at 6:31 AM
When exectuing a bash script with multiple echo commands, all_scheduler_job_run_details.output contains all of the echo statements.
But if there is a line comment after an echo, echos that follow the commnet are not included in .OUTPUT column. Why?
echo "a"
echo "b"
.OUTPUT contains:
a
b
echo "a"
# comment
echo "b"
.OUTPUT contains:
a
Post a Comment