Shulou(Shulou.com)05/31 Report--

This article mainly introduces ORACLE how to create jobs, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

First, use Jobs

The so-called JOBS is actually the execution scheduling of one (or more) tasks managed by Scheduler.

1.1 create Jobs

To create a Jobs through the DBMS_SCHEDULER package, you use its CREATE_JOB process. When creating a Job, the user can specify the task to be executed, scheduling information (when to execute, execution cycle, end date, etc.), and other task-related properties. CREATE_JOB procedure calls are relatively simple, such as:

SQL > BEGIN

2 DBMS_SCHEDULER.CREATE_JOB (

3 job_name = > 'INSERT_TEST_TBL'

4 job_type = > 'STORED_PROCEDURE'

5 job_action = >'P _ INSERT INTOTEST'

6 start_date = > sysdate

7 repeat_interval = > 'FREQ=DAILY;INTERVAL=1')

8 END

9 /

The PL/SQL process completed successfully.

In fact, if you have permission, users can also create other JOB under SCHEMA, as long as you specify JOB_NAME and follow the format of schema.job_name. Note that the CREATED of the JOB created in this case may not be the same as the OWNER.

When creating a JOB using the CREATE_JOB process, there are many parameter values that can be specified, but in most cases the user can specify only some of the parameters to meet the requirements.

The parameters specified in the above example represent the following meanings:

JOB_NAME: specify the name of the task, a required value, and make sure that the specified name is unique.

JOB_TYPE: the type of operation performed by the task, a required value, and the following optional values:

PLSQL_BLOCK: indicates that the task is executing an PL/SQL anonymous block.

STORED_PROCEDURE: indicates that the task executes an ORACLE procedure (including PL/SQL PROCEDURE and JAVA PROCEDURE), which is the parameter value specified in this example.

EXECUTABLE: indicates that the task executes an external program, such as an operating system command.

CHAIN: indicates that the task is executing a CHAIN.

JOB_ACTION: a required value for the operation performed by the task, which should match the parameters specified in the JOB_TYPE type.

For example, for PL/SQL anonymous blocks, you can place a specific representative of the PL/SQL block here, similar to DECLARE.. BEGIN... end; if it is an ORACLE procedure, you should specify a specific procedure name here, and note that because the task is executed, even if there are parameters such as OUT in the procedure, there will be no output when actually executed.

START_DATE: specify the time when the task is executed for the first time. This parameter can be empty. If it is empty, the task will be executed immediately. The effect is equivalent to specifying the parameter value as SYSDATE.

REPEAT_INTERVAL: specify how often the task will be executed, such as how long it will be triggered to execute again. This parameter can also be empty, which means that the currently set task is executed only once. The REPEAT_INTERVAL parameter needs to be talked about, because it is very different from the INTERVAL parameter in the standard JOB. By contrast, the syntax structure of the REPEAT_INTERVAL parameter is much more complex. The most important keywords are FREQ and INTERVAL.

The FREQ keyword is used to specify the time period of the interval. Optional parameters are: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY, which represent year, month, week, day, hour, minute, second and other units, respectively.

The INTERVAL keyword is used to specify the frequency of intervals, with values ranging from 1 to 99.

For example, REPEAT_INTERVAL= > 'FREQ=DAILY;INTERVAL=1'; means that it is executed once a day, and if you change INTERVAL to 7, it means that it is executed every 7 days, which is equivalent to FREQ=WEEKLY;INTERVAL=1.

In general, to create a JOB using DBMS_SCHEDULER.CREATE_JOB, you need to specify at least the first three of the above parameters. In addition, you can specify the following parameters when CREATE_JOB:

NUMBER_OF_ARGUMENTS: specify the number of parameters that need to be attached when the JOB is executed. 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, because incidental parameters are not supported in the above two cases.

END_DATE: specifies the expiration time of the task. The default value is NULL. When a task expires, the STATE of the task is automatically modified to COMPLETED,ENABLED and set to FALSE. If this parameter is set to empty, the task will never expire and will be repeated according to the period set by the REPEAT_INTERVAL parameter until the set MAX_RUNS or MAX_ values are reached.

JOB_CLASS: specifies the CLASS associated with the task. The default value is DEFAULT_JOB_CLASS. For information about JOB CLASS, follow up on this series.

ENABLED: specifies whether the task is enabled. The default value is FALSE. The FALSE status indicates that the task will not be executed unless it is manually invoked by the user, or the user changes the status of the task to TRUE.

AUTO_DROP: when this flag is set to TRUE, ORACLE automatically deletes the created task when the condition is met

The task has expired

The maximum number of runs of the task has reached the set value of MAX_RUNS

The task does not specify a REPEAT_INTERVAL parameter and is only run once

The default value of this parameter is TRUE. Users can manually specify the flag as FALSE when executing the CREATE_JOB process. When the parameter value is set to FALSE, the task will not be automatically deleted even if the above-mentioned conditions are met. In this case, the only situation that can cause the task to be deleted is that the user actively calls the DROP_JOB procedure.

COMMENTS: sets the comment information for the task. The default value is NULL.

The above example creates a new JOB, but this JOB is different from a normal JOB. Querying the USER_JOBS view cannot find the information about the JOB you just created, because this JOB is a JOB managed by SCHEDULER. To query the JOS managed by SCHEDULER, you should use USER_SCHEDULER_JOBS (of course, ALL_SCHEDULER_JOBS and DBA_SCHEDULER_JOBS are also available), for example:

SQL > select job_name,job_type,job_action,to_char (start_date,'yyyy-mm-dd hh34:mi:ss'), repeat_interval,enabled,state from user_scheduler_jobs

JOB_NAME JOB_TYPE JOB_ACTION TO_CHAR (START_DATE, REPEAT_INTERVAL ENABL STATE

-

INSERT_TEST_TBL STORED_PROCEDURE P_INSERTINTOTEST 2009-07-27 13:46:50 FREQ=DAILY;INTERVAL=1 FALSE DISABLED

However, careful people may find that although JOB has been successfully created, it has not been implemented. What is going on? In fact, the reason is very simple, remember the ENABLED parameter mentioned in the previous introduction to the CREATE_JOB process, when not explicitly specified, the default value of this parameter is false,JOB will not run naturally. If you encounter such a situation, how to modify it?

Thank you for reading this article carefully. I hope the article "how to create jobs with ORACLE" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.