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!!


1 Responses to “How to execute a Shell Script from PLSQL? (DBMS_SCHEDULER)”

bsturmer said...
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

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.