Executing Shell (bash) from PL/SQL

oracleplsql

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'.

Best Answer

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;
/
Related Question