Oracle sets a scheduled task JOB to schedule and execute stored procedures or PL/SQL code blocks

Oracle sets a scheduled task JOB to schedule and execute stored procedures or PL/SQL code blocks

Currently, there are two methods for configuring scheduled job Scheduling in Oracle Database: dbms_scheduler to create job scheduling and dbms_job to create job scheduling. Dbms_scheduler job Scheduling is only available after 10 Gb. Oracle provides more powerful functions and flexible mechanisms/management to replace dbms_job. The two methods are described below.

1. dbms_scheduler create job scheduling.
-- Query
Select * from dba_scheduler_jobs;
-- Create a job
Begin
Dbms_scheduler.create_job (
Job_name => 'job _ myjob', -- job name
Job_type => 'stored _ PROCEDURE ', -- job type
Job_action => 'proc _ myproc', -- stored procedure name
Start_date => sysdate, -- execution start time
Repeat_interval => 'freq = DAILY; BYHOUR = 9; BYMINUTE = 30; BYSECOND = 0', -- next execution time, by day, the stored procedure proc_myproc is executed at 09:30:00 every day
Comments => 'test job', -- Comment
Auto_drop => false -- whether to automatically delete a job after it is disabled
);
End;
-- Run
Begin
Dbms_scheduler.run_job ('job _ myjob ');
End;
-- Enable
Begin
Dbms_scheduler.enable ('job _ myjob ');
End;
-- Disable
Begin
Dbms_scheduler.disable ('job _ myjob ');
End;
-- Distinct
Begin
Dbms_scheduler.drop_job (job_name => 'job _ myjob', force => TRUE );
End;

2. dbms_job create job scheduling.
-- Query
Select * from dba_jobs;
Select * from all_jobs;
Select * from user_jobs;
Select * from dba_jobs_running;
-- Create a job
Declare
Job_id number; -- declares an out variable.
Begin
-- Execute the Stored Procedure proc_myproc at 09:30:00 every day on a daily basis, and output a job_id variable whose value is the ID number of the job.
Dbms_job.submit (
Job_id, -- the parameter is the output parameter returned by the submit () process, which uniquely identifies a job. Generally, a variable is defined and can be queried in the user_jobs view.
'Proc _ myproc; ', -- the parameter is the PL/SQL code block to be executed, the name of the stored procedure, and so on.
Sysdate, -- the parameter indicates when the job will be run.
'Trunc (SYSDATE + 1) + (9*60 + 30)/(24*60) '-- when the job will be re-executed.
);
-- Print the ID number of the job
Dbms_output.put_line (job_id );
End;
-- Run job
Begin
-- This 7 is job_id. Change the job number for you.
Dbms_job.run (7 );
End;
-- Enable job
Begin
Dbms_job.broken (7, false );
End;
-- Disable job
Begin
Dbms_job.broken (7, true );
End;
-- Delete a job
Begin
Dbms_job.remove (7 );
End;

Conclusion: We recommend that you use dbms_scheduler to create job scheduling. Job Scheduling can regularly execute PL/SQL code blocks, stored procedures, and so on.