What are the scheduled execution methods in Oracle stored procedures?

The following articles mainly introduce two specific methods for regular execution in Oracle stored procedures. One is to write shell scripts to perform regular execution in the background using crontab correctly, the other is to teach you how to add a job correctly. The following is the specific content of the article.

There are two ways to regularly execute Oracle stored procedures:

1. Write a shell script and use crontab to periodically execute the script in the background.

A) shell script

 
 

  
  
Bash-2.03 $ more pr_merge_query_report.sh
  
  
#! /Bin/sh
  
  
ORACLE_BASE =/oracle/home specifies the environment variable)
  
  
ORACLE_HOME =/oracle/home/data
  
  
NLS_LANG = american_america.ZHS16GBK
  
  
PATH =/usr/local/bin:/usr/sbin:
  
  
/Sbin:/usr/local/bin:/opt/EMCpower/bin:
  
  
/Etc:/oracle/home/data/bin:/usr/ccs/bin:/usr/openwin/bin:
  
  
/Usr/local/bin:/sbin:/usr/bin:
  
  
/Usr/local/bin:/usr/local/sbin:/export/home/bss/. db_conf
  
  
Export ORACLE_BASE ORACLE_HOME NLS_LANG PATH
  
  
Sqlplus-s username/password <
  
  
DECLARE
  
  
V_date VARCHAR2 (10); V_CODE VARCHAR2 (255); V_MSG VARCHAR2 (255); V_ERR VARCHAR2 (255 );
  
  
BEGIN
  
  
V_date: = to_char (sysdate, 'yyyymmdd ');
  
  
If substr (v_date, 7,2)> = 01 AND substr (v_date, 7,2) <= 04
  
  
THEN
  
  
ACCOUNT. PR_MERGE_QUERY_REPORT (to_char (SYSDATE, 'yyyymmd')-1, to_char (SYSDATE, 'yyyymmdd'), V_CODE, V_MSG, V_ERR );
  
  
ELSE
  
  
ACCOUNT. PR_MERGE_QUERY_REPORT (to_char (SYSDATE, 'yyyymmd'), to_char (SYSDATE, 'yyyymmdd'), V_CODE, V_MSG, V_ERR );
  
  
End if;
  
  
END;
  
  
/
  
  
Exit;
  
  
!
 
 

B) crontab

Use crontab-e to edit

30 22 ***/export/home/bss/pr_merge_query_report.sh>/export/home/bss/log/mergequery. log 2> & 1

Time Format: minute hour day month week

2. Add job

With pl/SQL developer, you can easily add regularly executed Oracle stored procedures.

Example:

 
 

  
  
begin  
  
  
sys.dbms_job.submit(job => :job,  
  
  
what => 'RATING.JOB_PRESS;',  
  
  
next_date => to_date('01-01-4000', 'dd-mm-yyyy'),  
  
  
interval => 'TRUNC(SYSDATE+1)');  
  
  
sys.dbms_job.broken(job => :job,  
  
  
broken => true,  
  
  
next_date => to_date('01-01-4000', 'dd-mm-yyyy'));  
  
  
commit;  
  
  
end;   
 
 

The above content is an introduction to the regular execution methods of Oracle stored procedures. I hope you will get some benefits.