Unanswered: load a conditional script from sqlplus?
We have two jobs in our 9i OEM on Win2k-- one to incrementally load data every 10 minutes (takes 30 seconds), one to reload nightly at 3:00 AM (takes 1.5 hours). Since the Oracle scheduler is not very powerful, we don't have an easy way to tell the incremental load to stop between 3 and 5 AM. We don't want the load to run at the same time as the reload, as the jobs clash.
The only difference between the 2 jobs is the batch file they call to do the data load. What we are thinking is to combine them into one job, with a way to conditionally load the correct script based on the time. (this way OEM will skip the next job executions until the reload is done).
I figured out a simple function in plsql, (simple code snippet below) but I can't call the batch file from within plsql. I think I need a way to do the same thing from SQLPLUS, which I can't figure out. Or is there another way to solve this problem?
begintime := TO_DATE(TO_CHAR(currenttime, 'DD-MON-YY') || ' 03:00', 'DD-MON-YY HH24:MI');
endtime := TO_DATE(TO_CHAR(currenttime, 'DD-MON-YY') || ' 03:10', 'DD-MON-YY HH24:MI');
IF(currenttime BETWEEN begintime AND endtime) THEN
script := 'reload';
script := 'load';
job IN BINARY_INTEGER,
what IN VARCHAR2);
Create an empty procedure, and pass it to be executed by the Oracle job scheduler before the load begins. Then when the load completes, check that and submit the real job. WHAT is the PL/SQL procedure to run.