Oracle Scheduler Jobs

Create Jobs Syntax:

Jobs are created using the DBMS_SCHEDULER package, which is the CREATE_JOB process. For example:

1. SQL>BEGIN
2. 2 DBMS_SCHEDULER.CREATE_JOB (
3. 3 job_name =>'Insert _ TEST_TBL',
4. 4 job_type =>'Stored _ PROCEDURE',
5. 5 job_action =>'P _ INSERT into Test',
6. 6 start_date => sysdate,
7. 7 repeat_interval =>'Freq = DAILY; INTERVAL = 1');
8. 8END;
9. 9/
11. The PL/SQL process is successfully completed.

In fact, if you have permissions, you can also create jobs in other schemas. You only need to specify JOB_NAME in the format of SCHEMA. job_name.

The parameters specified in the preceding example have the following meanings:

• JOB_NAME: the name of the specified task. A required value. Ensure that the specified name is unique.

• JOB_TYPE: Operation Type of a task. It is a required value and has the following optional values:
  • PLSQL_BLOCK: indicates that the task runs a PL/SQL anonymous block.
  • STORED_PROCEDURE: indicates that the task executes the Oracle process (including PL/SQL PROCEDURE and JAVA PROCEDURE). This parameter value is specified in this example.
  • EXECUTABLE: indicates that the task runs an external program, such as an operating system command.
  • CHAIN: indicates that the task runs a CHAIN.

• JOB_ACTION: The operation performed by the task. A required value must match the parameter specified in the JOB_TYPE. For example, for PL/SQL anonymous blocks, the specific representatives of PL/SQL blocks can be placed here, similar to DECLARE .. BEGIN .. this type of END. If it is an ORACLE process, the specific process name should be specified here. Note that, due to task execution, there will be no output even if there are parameters such as OUT in the process.

• START_DATE: specifies the time when the task is executed for the first time. This parameter can be blank. If it is null, the task is executed immediately. The effect is equivalent to specifying this parameter value as SYSDATE.

• REPEAT_INTERVAL: Specifies the frequency of task execution, for example, how long it will be triggered for re-execution. This parameter can also be null. If it is null, it indicates that the current set task is only executed once. The REPEAT_INTERVAL parameter needs to be discussed. The syntax structure of the REPEAT_INTERVAL parameter is much more complicated. The most important keywords are FREQ and INTERVAL.
  • The FREQ keyword is used to specify the interval. Optional parameters include: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY, year, month, week, day, hour, minute, second, and other units.
  • The INTERVAL keyword is used to specify the INTERVAL frequency. The value range of the specified value ranges from 1 to 99.

  For example: REPEAT_INTERVAL => 'freq = DAILY; INTERVAL = 1'; indicates that the execution is performed once every day. If you change INTERVAL to 7, the execution is performed every 7 days. The effect is equivalent to FREQ = WEEKLY; INTERVAL = 1.

Generally, to create a JOB using DBMS_SCHEDULER.CREATE_JOB, you must specify at least the first three items in the preceding parameters. You can also specify the following parameters during CREATE_JOB:

• NUMBER_OF_ARGUMENTS: specifies the number of parameters that need to be included during JOB execution. The default value is 0. Note that when the JOB_TYPE column value is PLSQL_BLOCK or CHAIN, this parameter must be set to 0, in either case, parameters are not supported.

• END_DATE: Specifies the task expiration time. The default value is NULL. After the task expires, the STATE of the task is automatically changed to COMPLETED, and ENABLED is set to FALSE. If this parameter is set to null, it indicates that the task will never expire and will be executed repeatedly according to the cycle set by REPEAT_INTERVAL until the set MAX_RUNS or MAX_FAILURES value is reached.

• JOB_CLASS: Specifies the CLASS associated with the task. The default value is DEFAULT_JOB_CLASS. For information about job class, follow the subsequent articles in this series.

• ENABLED: Specifies whether the task is ENABLED. The default value is FALSE. "FALSE" indicates that the task is not executed unless it is manually called or changed to "TRUE.

• AUTO_DROP: When this flag is set to TRUE, ORACLE will automatically delete the created task when the conditions are met.
  • The task has expired;
  • The maximum number of running tasks has reached the value set by MAX_RUNS;
  • The REPEAT_INTERVAL parameter is not specified for the task and only runs once;

• COMMENTS: Set the COMMENTS of a task. The default value is NULL.

Example:

First, create a stored procedure to insert a record into the test table.

1. SQL>Create Or Replace ProcedureP_testIs
2. 2Begin
3. 3Insert IntoTestValues(1,'Test');
4. 4EndP_test;
5. 5/
7. The process has been created.

Create jobs

1. SQL>ExecDbms_scheduler.create_job (job_name =>'J _ Test', Job_type =>'Stored _ procedure', Job_action =>'P _ Test', Start_date => sysdate, repeat_interval =>'Freq = daily; interval = 1');
3. The PL/SQL process is successfully completed.
5. SQL>SelectJob_name, job_type, job_action, start_date, repeat_interval, end_date, enabled, auto_drop, state, run_count, max_runsFromUser_scheduler_jobs;
7. JOB_NAME JOB_TYPE JOB_ACTION START_DATE REPEAT_INT END_DATE enabl auto _ STATE RUN_COUNT MAX_RUNS
8. -----------------------------------------------------------------------------------------------------------------------------------
9. J_TEST STORED_PROCEDURE p_test 13-11-11 freq = dailyFALSE TRUEDISABLED 0
10. 06.07.07.; interval =
11. 000000 on 1
12. Noon +

However, you may find that the JOB is successfully created but not executed. What is the problem? In fact, the reason is very simple. Do you still remember the ENABLED parameter mentioned in the CREATE_JOB process? If it is not explicitly specified, the default value of this parameter is false, and the JOB will naturally not run. In this case, how can we modify it?

1. ExecDbms_scheduler.enable ('J _ Test');-- Enable jobs
3. ExecDbms_scheduler.disable ('J _ Test');-- Disable jobs
5. ExecDbms_scheduler.run_job ('J _ Test');-- Execute jobs
7. ExecDbms_scheduler.stop_job ('J _ Test');-- Stop jobs
9. ExecDbms_scheduler.drop_job ('J _ Test');-- Delete jobs

Modify jobs

1. SQL>ExecDbms_scheduler.set_attribute ('J _ Test','Start _ date', To_date ('2017-11-13 06:28:15','Yyyy-mm-dd hh24: mi: ss'));
2. <P>
3. The PL/SQL process is successfully completed. </P> <p> SQL>ExecDbms_scheduler.enable ('J _ Test');
4. </P>
5. SQL>SelectJob_name, job_type, job_action, start_date, repeat_interval, end_date, enabled, auto_drop, state, run_count, max_runsFromUser_scheduler_jobs;
7. JOB_NAME JOB_TYPE JOB_ACTION START_DATE REPEAT_INT END_DATE enabl auto _ STATE RUN_COUNT MAX_RUNS
8. -----------------------------------------------------------------------------------------------------------------------------------
9. J_TEST STORED_PROCEDURE p_test 13-11-11 freq = dailyTRUETRUESCHEDULED 0
10. 06.28.15.; interval =
11. 000000 on 1
12. Noon +

Although the SET_ATTRIBUTE process has only three parameters, there are many attribute values that can be modified. The following are some of the frequently used attributes:

• LOGGING_LEVEL: Specifies the log information level recorded for jobs execution.

  SCHEDULERManagementSpecifically, you can also select the level of information recorded in the log. There are three options:

  • DBMS_SCHEDULER.LOGGING_OFF: Disable Logging;
  • DBMS_SCHEDULER.LOGGING_RUNS: records the running information of the task;
  • DBMS_SCHEDULER.LOGGING_FULL: records all relevant information about the task, including not only the running status of the task, but also the creation and modification of the task.

  Tip: To view jobs managed by SCHEDULER, You can query them in two views: USER_SCHEDULER_JOB_LOG and USER_SCHEDULER_JOB_RUN_DETAILS.

• RESTARTABLE: Specifies whether jobs can be restarted in due time after an error occurs during running. If this parameter is not explicitly specified during Task creation, it is set to FALSE by default. If it is set to TRUE, it indicates that an error occurs during task running and the task will still be started at the next running time, in addition, if an error occurs, the job will continue to run again. However, if the connection fails six times, the job will stop.

• MAX_FAILURES: specifies the maximum number of consecutive errors of jobs. This parameter value ranges from 1 to 1000000. By default, this parameter is set to NULL, which indicates no limit. After the specified number of errors is reached, the job is automatically disable.
• MAX_RUNS: specifies the maximum number of jobs. This parameter value can be specified from 1 to 1000000. By default, this parameter is set to NULL, indicating no limit (only the number of running times is unlimited, and whether the actual job will continue to run, it is still subject to parameters such as end_date and max_failures ). After the specified number of running times is reached, the job will be automatically disable and set to COMPLETED.

• JOB_TYPE: specifies the type of the job to be executed by the job. There are four optional values: "PLSQL _ BLOCK", "STORED _ PROCEDURE", "EXECUTABLE", and "CHAIN.

• JOB_ACTION: Specifies the job to be executed by the job. The value specified by this parameter depends on the value of JOB_TYPE. For example, if JOB_TYPE is set to "STORED _ PROCEDURE", the parameter value must be the process name in ORACLE.
• START_DATE: Specifies the initial start time of a job.
• END_DATE: specifies the time when the job stops running. This parameter is also associated with AUTO_DROP. If AUTO_DROP is set to TRUE, the job will be automatically deleted once the job stops running. Otherwise, the job will always exist, however, the status is changed to COMPLETED.

You can use the DBMS_SCHEDULER.SET_ATTRIBUTE process to set these parameters. Note that apart from manually created jobs,DatabaseJobs may also be automatically created during running. It is not recommended to modify such jobs unless necessary. To determine whether jobs are created by users or automatically by databases, you can use the SYSTEM column in the * _ SCHEDULER_JOBS view. If this column is displayed as TRUE, it indicates that jobs are created by the SYSTEM.

1 2 3 Next Page

[Content navigation]
Page 1: basic usage	Page 7: shell scripts
Page 2: Repeat Interval Parameters