1

Does anyone know how to execute a shell script from a PL/SQL procedure or function? Let's say the shell script is '/usr/bin/bash/convert.sh'.

  • Execute a shell script from a PL/SQL procedure: dba-oracle.com/t_execute_shell_script_plsql_procedure.htm (hope this help). If not, can you provide more info ? – Luan Huynh Mar 17 '16 at 2:31
  • Hi Luan, Thanks for the link, though I haven't tried that yet. Do you have any working samples for the approach in the link? I have created a shell script to convert pdf to ps(post script), then the input filepath will be passed from a PL/SQL procedure/function. I've been thinking if the shell script can be directly invoked from pl/sql. Do you have any other thoughts on how to achieve this one? Thanks a lot! – user159486 Mar 17 '16 at 3:09
  • DBMS.SCHEDULER is straightforward. Try this link for an example asktom.oracle.com/pls/asktom/… – BriteSponge Mar 17 '16 at 9:07
3

You need to create an external job for that.

Techniques for External Jobs

First, you need to create a credential, with the OS user and password in whose name the job will run:

begin
  dbms_scheduler.create_credential
  (
    credential_name => 'my_credential',
    username        => 'oracle',
    password        => 'Oracle123'
  );
end;
/

After that you can use this credential when creating an external job. For example calling your convert script with 2 arguments (input and output file)

begin
  dbms_scheduler.create_job
  (
    job_name             => 'convert_job',
    job_type             => 'executable',
    number_of_arguments  => 2,
    job_action           => '/usr/bin/bash/convert.sh',
    auto_drop            => true,
    credential_name      => 'my_credential'
  );

  dbms_scheduler.set_job_argument_value('convert_job', 1, '/home/oracle/file.in');
  dbms_scheduler.set_job_argument_value('convert_job', 2, '/home/oracle/file.out');

  dbms_scheduler.enable('convert_job');
end;
/
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.