How to Run Unix Script from within PL/SQL oracle 10g - Solaris 10

How to Run Unix Script from within PL/SQL oracle 10g - Solaris 10

This example will demonstrate how to run an a unix script from within PL/SQL. The example uses DBMS_SCHEDULER builtin oracle package.The example was tested on Solaris 10 with Oracle 10g instance.

$ sqlplus / as sysdba
SQL>
CREATE OR REPLACE
  PROCEDURE RUN_OS_SCRIPT( in_os_command IN VARCHAR2)
  IS
   v_prg          NUMBER;
   v_jobs         NUMBER;
   error_message  VARCHAR2(500);
   v_count99      NUMBER;
   v_job_action   VARCHAR2(100);
   v_arg          VARCHAR2(50);
  BEGIN
   v_job_action := in_os_command;
   v_prg         := 0;
   v_jobs        := 0;
   v_count99     := 0;
   BEGIN
      dBMS_SCHEDULER.stop_job (job_name => 'RUN_UNIX_SCRIPT_JOB' );
   EXCEPTION
   WHEN OTHERS THEN
      NULL;
   END;
   --=========================================================================
   -- This loop will make sure that the job is not already running. This seems to be a bug in 10g that
   -- stop and drop commands fail to stop or drop schedules / jobs immediately.
   -- To oversome this problem I have added this loop to make sure the next run should be successful.
   --=========================================================================
   LOOP
      IF v_count99 > 2000 THEN
         EXIT;
      END IF;
      v_count99 := v_count99+1;
      SELECT COUNT(*)
      INTO v_prg
      FROM sys.dba_scheduler_programs
      WHERE upper(PROGRAM_NAME) = upper('RUN_UNIX_SCRIPT_PRG') ;
      SELECT COUNT(*) INTO v_jobs
      FROM sys.dba_scheduler_jobs
      WHERE upper(JOB_NAME) = upper( 'RUN_UNIX_SCRIPT_JOB' ) ;
      IF v_prg > 0 OR v_jobs > 0
       THEN
         BEGIN
            DBMS_SCHEDULER.stop_job (job_name => 'RUN_UNIX_SCRIPT_JOB' );
            DBMS_SCHEDULER.drop_program  ( program_name => 'RUN_UNIX_SCRIPT_PRG' , force => TRUE);
            DBMS_SCHEDULER.drop_schedule ( schedule_name => 'RUN_UNIX_SCRIPT_SCHEDULE' , force => TRUE);
            DBMS_SCHEDULER.drop_job      ( job_name => 'RUN_UNIX_SCRIPT_JOB' , force => TRUE);
         EXCEPTION
         WHEN OTHERS THEN
            NULL;
         END;
      ELSE
         EXIT ;
      END IF;
   END LOOP;
   --========================================================================

   --========================================================================
   -- Create a new schedule and define a job to run
   --========================================================================
   DBMS_SCHEDULER.CREATE_PROGRAM( program_name => 'RUN_UNIX_SCRIPT_PRG' , program_type => 'EXECUTABLE',
   program_action => v_job_action, enabled         => FALSE, number_of_arguments => 0, comments => 'RUN RUN_UNIX_SCRIPT' );
   DBMS_SCHEDULER.enable (name                     => 'RUN_UNIX_SCRIPT_PRG' );
   DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name   => 'RUN_UNIX_SCRIPT_SCHEDULE', start_date => sysdate + .001,  repeat_interval => 'freq=hourly; byminute=0', end_date => NULL, comments => 'test schedule');
   DBMS_SCHEDULER.CREATE_JOB ( job_name            => 'RUN_UNIX_SCRIPT_JOB', program_name => 'RUN_UNIX_SCRIPT_PRG' ,   schedule_name => 'RUN_UNIX_SCRIPT_SCHEDULE', enabled=> true, auto_drop=> false);
   DBMS_SCHEDULER.run_job (job_name                => 'RUN_UNIX_SCRIPT_JOB' );
   error_message := 'job done';
   dbms_output.put_line(error_message);

   --===================================================================
   -- Drop schedule after the job is completed
   --===================================================================
   BEGIN
      DBMS_SCHEDULER.drop_program ( program_name     => 'RUN_UNIX_SCRIPT_PRG' , force => TRUE);
      DBMS_SCHEDULER.drop_schedule ( schedule_name   => 'RUN_UNIX_SCRIPT_SCHEDULE' , force => TRUE);
      DBMS_SCHEDULER.drop_job ( job_name             => 'RUN_UNIX_SCRIPT_JOB' , force => TRUE);
    EXCEPTION
    WHEN OTHERS THEN
      NULL;
   END;
EXCEPTION
 WHEN OTHERS THEN
   error_message := v_count || '    ' ||SUBSTR(SQLERRM,1,100);
   dbms_output.put_line(error_message);
END;
/

Execute following code to run the operating system command / script.

  BEGIN
     RUN_OS_SCRIPT('/data1/users/scripts/copy_arch_logs.sh' );
  END;
/