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

    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?

    TIA




    ...
    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';
    ELSE
    script := 'load';
    END IF;
    ...

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    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:
    Code:
    BEGIN
    
    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);
    
    END;
    /

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

    DBMS_JOB.WHAT (
    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.


    Hope that helps,

    clio_usa
    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
  •