In some situations, you need to run programs that are installed on the host machine from an Oracle database. You can do that for example using Java classes. But there is another easy way to achieve this. With the help of the „dbms_scheduler“ package. As Example I write PL/SQL block to get the differences between tow files using the diff shell command.

DECLARE
v_file_left_full_name VARCHAR2(280) := '/tmp/file1';
v_file_right_full_name VARCHAR2(280) := '/tmp/file2';
v_file_out_full_name VARCHAR2(280) := '/tmp/diff_result';
v_script_full_name VARCHAR2(280) := '/tmp/filediff.sh';
v_os_program       VARCHAR2(280) := '/bin/bash';
--
v_job_name VARCHAR2(30) := 'FILE_DIFF';
v_os_credential VARCHAR2(30) := 'OS_CRED';

--====Begin====--
BEGIN
 
  BEGIN
    dbms_scheduler.drop_job(job_name => v_job_name);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -27475 THEN
        dbms_output.put_line('--');
      ELSE
        RAISE;
      END IF;
  END ;
   BEGIN
    DBMS_SCHEDULER.drop_credential(v_os_credential);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('1- ' || SQLERRM);
      NULL;
  END ;
  DBMS_SCHEDULER.create_credential(v_os_credential,'oracle','oracle');
  DBMS_SCHEDULER.CREATE_JOB(job_name          => v_job_name,
                            job_type          => 'EXECUTABLE',
                            job_action        => v_os_program,
                            number_of_arguments => 4,
                            credential_name=>   v_os_credential,
                            enabled           => FALSE);
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job_name,
                                        1,
                                        v_script_full_name);
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job_name,
                                        2,
                                        v_file_left_full_name);
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job_name,
                                        3,
                                        v_file_right_full_name);
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job_name,
                                        4,
                                        v_file_out_full_name);
  dbms_scheduler.run_job(job_name => v_job_name);
END;

#!/bin/bash
diff $1 $2 > $3
exit 0