oracle job to run every thurday morning and sunday morning

try this...


CASE
           WHEN NEXT_DAY(SYSDATE, 'Thurs') < NEXT_DAY(SYSDATE, 'Sun')
           THEN
               TRUNC(NEXT_DAY(SYSDATE, 'Thurs')) + 1 / 24
           ELSE
               TRUNC(NEXT_DAY(SYSDATE, 'Sun')) + 1 / 24
       END

that will generate an interval so that each run will be at either 1am Thursday or 1am Sunday

I generally wrap special case scheduling like that into a function  then call the function in the interval

especially with complicated intervals because the field can only be 200 characters

I created a function as ..

CREATE OR REPLACE FUNCTION NextExportSchedule RETURN DATE IS
BEGIN
    CASE
           WHEN NEXT_DAY(SYSDATE, 'Thurs') < NEXT_DAY(SYSDATE, 'Sun')
           THEN
               return TRUNC(NEXT_DAY(SYSDATE, 'Thurs')) + 1 / 24;
           ELSE
               return TRUNC(NEXT_DAY(SYSDATE, 'Sun')) + 1 / 24;
     END case;
END;
/

how do i call that?? from my job..

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X
     ,what      => 'My ExportProcedure'
     ,next_date =>'????'
     ,interval  => '????'
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

also if i want to call my proc at 3:00am its 3/24?? just want to make sure..thanks

Thanks very much....Excellent..

glad I could help

also note,  in 10g  you have the dbms_scheduler which offers very sophisticated scheduling options.

this one wasn't too bad so dbms_job was sufficient but if your requirements but you may want to investigate the scheduler's advanced capabilities