Run shell script within oracle procedure

Question

I have a script /home/load_data.sh that I want to run from my procedure, only if my IF statement is not satisfied:

create or replace
PROCEDURE   RD_ODS_REFRESH_LOG 
IS
BEGIN 
 FOR i IN 
 (
    SELECT RESULT FROM USERS
    WHERE trunc(COMPLETED_DATE) = trunc(SYSDATE -1) 
 )
    LOOP
      IF i.RESULT = 'FAIL' 
      THEN 
          EMAIL
          (
              '[email protected]', 
              'daily Refresh report', 
              'FAILED', 
              'Todays daily Refresh Failed.'
          );
      ELSE
          SYS.DBMS_SCHEDULER.create_job 
          (
              job_name              => 'RD_ODS_REFRESH_LOG',
              job_action            => '/home/load_data.sh',
              job_type              => 'executable',
              number_of_arguments   => 0,
              start_date            => SYSDATE,
              repeat_interval       => 'FREQ=SECONDLY; INTERVAL=1',
              enabled               => FALSE,
              auto_drop             => FALSE,
              comments              => 'run load data shell script'
          );
          SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
          ( 
              name => 'RD_ODS_REFRESH_LOG', 
              attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FULL
          );
          SYS.DBMS_SCHEDULER.enable
          (
              name => 'RD_ODS_REFRESH_LOG'
          );
      END IF;
    END LOOP;
END RD_ODS_REFRESH_LOG;

I am wondering how to put all of this together so that when IF is not satisfied, it jumps to ELSE statement and it executes the above code?

Can I use something else other than SCHEDULER? After all all I want is to be able to run my /home/load_data.sh script in ELSE case.

I use Oracle11g...

David Aldridge · Accepted Answer · 2013-07-16 16:10:58Z

1

It might be worth going in the other direction and making more use of scheduler, in particular of scheduler chains.

I say this because the sort of flow control that you show here -- do X if fail, otherwise do Y -- is tiresome to code, and with a scheduler chain you simply specify the next step to run if particular steps raise an error or not.

Once you get used to it it's pretty pain free, and very flexible and reliable -- I ran scheduler chains to load a data warehouse, executing the load and transform job every 60 seconds 24 hours a day for over a year, stopping the chains for maintenance, starting them again when maintenance is over, and all you need are a few monitoring and control scripts.

answered Jul 16, 2013 at 16:10

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Run shell script within oracle procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related