Crontab timed execution of Oracle stored procedures

Crontab timed execution of Oracle stored procedures

Requirements Description

We have an Oracle stored procedure that needs to be executed every month, generate a report, and then send it to the business unit, which we have implemented in the system's foreground interface (1-1), but the customer does not point the regeneration every time, resulting in the export report times out of exception (1-2).

Figure 1-1

Figure 1-2

Problem analysis

Since it is a stored procedure, we navigate to the stored procedure that corresponds to the request, as shown in

Figure 1-3

We look at its parameters, there are only two, one is the start time, which indicates the start time of the month, an end time, indicating the end time of the month, we just have to pass a parameter to it when the script executes it.

Problem solving

To solve this problem, my idea is to write all the logic that needs to be dealt with in the script, and then set a crontab to execute through the timed task.

First, look at how simple the plsql is to call this stored procedure, I define a startdate and EndDate, and then through the Oracle time function Last_day calculate the last day of the month, start the first day I was directly in the month splicing the ' 01 ', As for why Add_months (sysdate,-1) wants-1, is because the month's general execution last month.

Declare
StartDate VARCHAR2 (8);
EndDate VARCHAR2 (8);
Begin
Select To_char (Add_months (sysdate,-1), ' yyyymm ') | | ' 01 '
into StartDate from dual;
Select To_char (Last_day (Add_months (sysdate,-1)), ' YYYYMMDD ')
into EndDate from dual;
Proc_high_settle_rep_month (startdate,enddate);
End
/

Second, write the above logic into the SH script, we create a new file in the relevant path call_proc_high_settle_rep_month.sh, by executing the sqlplus, and then go to do the above content

The script reads as follows:

The zh in sqlplus indicates the database account name, * represents the password, zh10g represents the database instance name, is the connection name that you configured in Tnsnames.ora.

./issdata/application/appiss/.profile
echo "Get begging"
Date
sqlplus zh/***** @zh10g <<!
Declare
startdate varchar2 ();
enddate varchar2 ();
begin
Select To_char (add_months (sysdate,-1), ' yyyymm ') | | ' "
Into startdate from dual;
Select To_char (Last_day (Add_months (sysdate,-1)), ' YYYYMMDD ')
Into enddate from dual;
Proc_high_settle_rep_month (startdate,enddate);
end;
/
!
echo "Get Finished"

Third, the manual execution of this script, found that there is no error, can be executed.

SH call_proc_high_settle_rep_month.sh

We add to Crontab, crontab-e into crontab Edit, note script to write absolute path

5 * * sh/tmp/call_proc_high_settle_rep_month.sh

The above-mentioned crontab will execute this command on behalf of the 23:08 points of the 5th number per month.

The above 08 23 5 * * Represent minutes, hours, days, months, and weeks in turn.

At this point, the entire program has achieved all the logic, and later even if the business part of the future, you can also export the basic data, report out.

Crontab timed execution of Oracle stored procedures