Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002

    Question 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';
    END IF;

  2. #2
    Join Date
    Feb 2001
    NC, USA
    A quick and easy method is to set the job to 'BROKEN' just before the big load starts.

    The correct way to do that is using the DBMS_JOB package.

    For example:
    DBMS_JOB.BROKEN(your_little_job_id, TRUE);
    -- start big job here
    -- lot's of good stuff that takes over an hour
    -- end big job here
    DBMS_JOB.BROKEN(your_little_job_id, FALSE);

  3. #3
    Join Date
    Apr 2002
    California, USA
    Or you can use:

    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.

    Hope that helps,

    OCP - DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts