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

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

# PLS-00801: internal error [string]

apparently, pls-00801: internal error [phdcsql_print_kge_errors::parm pos] is one of the most annoying oracle errors. off course, phdcsql_print_kge_errors::parm pos is just an example that can be replcaed with any other undecipherable set characters.

Oracle’s official error description is:

Cause: This is a generic internal error that might occur during compilation or execution. The first parameter is the internal error number.

Action: Report this error as a bug to your Customer Support representative.

While it might be very tempting to contact Oracle Support, many users will prefer to try and solve the problem without opening a service request and waiting for an answer.
Basically, what this error means is that there is a problem with this query but Oracle is having problem describing the error. The result is a generic (and not really helpful ) error message.
Like any generic error description, many reasons can cause the problem. The only solution is trying to identify the problem yourself by trial and error.

Copy the query part of the code to a separate window and try to tweak it without changing the basics: change tables order, change aliases names, comment what you can or run it without variables. Even if this will not solve the problem, sometimes this can provide a more informative error message.
Using this method I have found several issues that caused pls-00801 internal error. I am writing a few in order to provide some list of things that can cause this error:

1) The query used a remote database using a db link and the database link user did not have proper permissions.
2) Extra not visible control characters that were added to the code during a problematic copy-paste from the web or from Word.
3) Extra characters that might have different meaning in oracle like:&,”,_ etc.
4) Extra spaces
5) Invalid objects on remote database
6) Several known Oracle bugs (search metalink for the error)
7) An error on a remote database can not be displayed.