Tag Archives: ORA-06512

How to schedule a job to run on a different hour on a specific date.

Leave a reply

Oracle jobs are a simple way to schedule procedure and processes. However, sometimes the scheduling requirements can get really complicated. A very common request is to run a job on a specific hour on weekdays and not to run it on the weekend. This request can be easily delivered using the “WHAT” parameter of the dbms_job.submit procedure.

BEGIN
  IF to_char(sysdate, 'D') BETWEEN 2 AND 6 THEN
      my_procedure ;
  END IF;
END;

However, what will you do if you need to run the procedure at a specific hour on Monday to Friday but on a different hour on the weekend?
In this case you will be happy to know that you can use CASE on the “INTERVAL” parameter of the dbms_job.submit procedure.

DECLARE
  my_job NUMBER;
BEGIN
  dbms_job.submit(job => my_job, 
    what => 'my_procedure ;',
 
     INTERVAL => 'case
                     when  to_number(to_char(sysdate, ''D'')) between 1 and 5 then 
                        TRUNC(SYSDATE+1) + 16 /24 
                     when  to_number(to_char(sysdate, ''D'')) between 6 and 7 then
                         TRUNC(SYSDATE+1) + 8 /24 
                  end'
 
 
    );
END;

remember that this calculation is for NEXT run not for the current. Therefore, you will need to take into consideration that the timinig for Monday will be calculated on Sunday (I know it sounds obvious but you will be surprised how many time you can do the same mistake).

While the case clause can handle many calculations and complications, there is one caveat. The case statement can handle only 200 characters. This becomes a problem when you try to write a longer statement. For example:

CASE
  WHEN  to_number(to_char(sysdate, 'D')) BETWEEN 1 AND 4 THEN 
      TRUNC(SYSDATE+1) + 16 /24 
  WHEN  to_number(to_char(sysdate, 'D'))= 5 THEN
      TRUNC(SYSDATE+1) + 13 /24     
  WHEN  to_number(to_char(sysdate, 'D'))= 6 THEN
      TRUNC(SYSDATE+2) + 16 /24          
  ELSE
      TRUNC(SYSDATE+1) + 16 /24 
 END

if you try to submit a job with this statement, you will probably get the following error

ORA-12899: VALUE too LARGE FOR COLUMN "SYS"."JOB$"."INTERVAL#" (actual: 257, maximum: 200)
ORA-06512: at "SYS.DBMS_IJOB", line 290
ORA-06512: at "SYS.DBMS_JOB", line 159
ORA-06512: at line 4

You can workaround this problem if you use a function to calculate the INTERVAL value.

CREATE OR REPLACE FUNCTION get_next_run_date RETURN DATE IS
 
res DATE;
BEGIN
SELECT
 
CASE
  WHEN  to_number(to_char(sysdate, 'D')) BETWEEN 1 AND 4 THEN
      TRUNC(SYSDATE+1) + 16 /24
  WHEN  to_number(to_char(sysdate, 'D'))= 5 THEN
      TRUNC(SYSDATE+1) + 13 /24
  WHEN  to_number(to_char(sysdate, 'D'))= 6 THEN
      TRUNC(SYSDATE+2) + 16 /24
  ELSE
      TRUNC(SYSDATE+1) + 16 /24
 END
 INTO res
 FROM dual
  ;
RETURN res;
 
END;

once the function is created, all you need to do is use it in your dbms_job submit under the INTERVAL parameter

DECLARE
  my_job NUMBER;
BEGIN
  dbms_job.submit(job => my_job, 
    what => 'my_procedure ;',
 
    INTERVAL => 'get_next_run_date'
    );
END