How to troubleshoot job interval input parameters are too long

This article analyzes how to resolve the problem of interval input parameters for a job based on an error code.

The Pl/sql code for the run job given is:

DECLARE
Job Binary_integer: =: job;
Next_date Date: =: mydate;
Broken BOOLEAN: = FALSE;
BEGIN
WHAT
: mydate: = next_date;
IF broken Then:b: = 1; Else:b: = 0; End IF;
      End;

It also explains how to use next_date as an output parameter of a stored procedure to specify the next run time for the next job.

The first time to see here, on the one hand is a sigh of such internal things, the General people are unable to get, only Tom such insiders can get, on the other hand, to admire Tom's skill, to tell the truth, even if the code to me, I can not think of such a clever method.

This reread part of the content, with a little new ideas, for the broken variable, can also do something?

Using a method similar to the above, you can control the behavior of the job so that the job is automatically stopped from executing once it is successfully run. Because a normal one-time job disappears from the job view after it runs, it is cumbersome to use the log table if you want to leave the running information. The following method is used to handle a one-time job, which makes it easy to keep the running information of the job:

sql> conn/@YANGTK as SYSDBA

is connected.

sql> GRANT EXECUTE on Dbms_lock to YANGTK;

The authorization was successful.

sql> CONN yangtk/yangtk@yangtk

is connected.

sql> CREATE OR REPLACE PROCEDURE p_test (P_broken out BOOLEAN) as
2 BEGIN
3 P_broken: = TRUE;
4 Dbms_lock. Sleep (5);
5 End;
      6/

Procedure has been created.

Sql> DECLARE
2 v_job number;
3 BEGIN
4 dbms_job. SUBMIT (V_job, ' p_test (broken); ', Sysdate, ' sysdate + 1/1440 ');
5 COMMIT;
6 end;
      7/

The PL/SQL process has completed successfully.

sql> COL WHAT FORMAT A30
sql> SELECT JOB, WHAT, Total_time, broken, failures from 
user_jobs;
JOB WHAT total_time B failures
---------------------------------------- 
6 p_test (broken); 3 N
sql> SELECT JOB, WHAT, Total_time, broken,
 failures from User_jobs;
JOB WHAT total_time B failures
---------------------------------------- 
      6 p_test (broken); 5 Y 0

In this way, after running once, the job no longer runs, but the job information is not lost and remains in the User_jobs view.