How to execute stored procedures in Oracle regularly

In enterprise-level applications, we cannot just rely on users to manually perform operations to achieve the required functionality. Therefore, scheduled tasks are essential, which are especially useful for performing background processing and data loading. Oracle database provides stored procedures and scheduled tasks to simplify the work of developers and administrators. In this article, we will focus on the method of using Oracle scheduled tasks to execute stored procedures regularly.

Oracle scheduled tasks

Scheduled tasks in the Oracle database can be created using the DBMS_SCHEDULER package. This package allows us to create jobs with complex scheduling requirements. It can support various frequencies such as hourly, daily, weekly, monthly, yearly, etc. Scheduled tasks can also be executed on a specific date and time, or even in different time zones. The following types of objects can be created using the DBMS_SCHEDULER package.

• Job (job): A task unit created when executing the DBMS_SCHEDULER.CREATE_JOB function, scheduled by the scheduler regularly or on request.
• Job class: A group of related tasks that share the same resource consumption limit rule.
• Resource consumption limit: Resource consumption rules used to control jobs and job classes.
• Chain: A sequence of jobs in which the completion of one job triggers the execution of another job.
• Session: The process associated with a job execution. Sessions can be logged and monitored to understand job execution.

In this article, we will focus on how to use DBMS_SCHEDULER to create a job and bind it to the stored procedure we want to execute.

Create a stored procedure

We first need to create a stored procedure to perform the task operation we want to complete. Below is a simple example of a stored procedure that will insert the current time into a table.

CREATE OR REPLACE PROCEDURE INSERT_TIME
AS 
BEGIN
  INSERT INTO TIME_LOG (LOG_TIME) VALUES (SYSDATE);
  COMMIT;
END INSERT_TIME;

Create job

With the stored procedure, we next need to create a job to schedule its execution. The job we are going to create will insert the current time every morning at 6 o'clock. Below is an example of using the DBMS_SCHEDULER.CREATE_JOB function to create this job.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
          job_name        => 'INSERT_TIME_JOB',
          job_type        => 'STORED_PROCEDURE',
          job_action      => 'INSERT_TIME',
          start_date      => SYSTIMESTAMP,
          repeat_interval => 'FREQ=DAILY; BYHOUR=6;',
          enabled         => TRUE,
          comments        => 'Insert current time every day at 6 AM');
END;

When using the CREATE_JOB function to create a job, we need to follow the following parameter description:

• job_name: The name of the job, which must be unique.
• job_type: The type of job, usually "STORED_PROCEDURE".
• job_action: The name of the stored procedure, package or executable file to be executed by the job.
• start_date: The start time of the job, which can be a date or a timestamp. If not specified, it defaults to the current time.
• repeat_interval: Specify the execution frequency and time interval of the job. In this example, "FREQ=DAILY; BYHOUR=6;" means that it is executed every day at 6 o'clock in the morning. Other options include hourly, weekly, monthly, yearly, etc. We can also use this parameter to specify the days on which the job should be executed, such as every Monday, Tuesday, and Thursday.
• enabled: Indicates whether the job is enabled.
• comments: A short description of the assignment.

Bind job to stored procedure

In the above example, we created a job and specified the stored procedure for its execution. Now, we need to bind this job to our stored procedure so that it executes the operation at the specified time. We can use the DBMS_SCHEDULER.SET_ATTRIBUTE function to accomplish this task.

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
          name => 'INSERT_TIME_JOB',
          attribute => 'program_action',
          value => 'INSERT_TIME;');
END;

In this example, we use the SET_ATTRIBUTE function to set the "program_action" attribute of the job "INSERT_TIME_JOB" to "INSERT_TIME". This will tell DBMS_SCHEDULER to call the INSERT_TIME stored procedure at a specific time.

We have learned how to use the DBMS_SCHEDULER package to create scheduled jobs and bind tasks in stored procedures. Now, we can wait for the scheduled task to execute and view the records in the TIME_LOG table.

Conclusion

Using the scheduled task function of the Oracle database can help us easily perform background processing tasks. The DBMS_SCHEDULER package provides many options to meet our specific application needs, such as repeated execution, parallel execution, failure recovery, etc. Additionally, it has the ability to execute jobs across different time zones, dates, weeks, months, etc., which makes scheduled tasks very flexible. In this article, we focus on how to use Oracle's scheduled tasks to execute stored procedures regularly. We hope this article can provide you with useful guidance and help you better understand the use and operation principles of scheduled tasks based on Oracle database.

The above is the detailed content of How to execute stored procedures in Oracle regularly. For more information, please follow other related articles on the PHP Chinese website!