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.