Shell executes the Oracle stored procedure to obtain the returned values of the stored procedure

For a small etl scheduling, colleagues need to return the execution status of the stored procedure and control whether the subsequent dependency is executed, I only returned the output parameters of the stored procedure in the shell script that calls and executes the stored procedure, and did not write a specific control process for everyone. If you continue development in this way, that is a small etl scheduling program. If you need it, contact me for help in 360263676.

The following describes the scripts: (ex_produre.sh)

1. Execute the Stored Procedure script

#! /Bin/bash
User_name = $1
User_pass = $2
Produre_name = $3
Statis_sign = $4
SQL _str ='
Sqlplus-S $ user_name/$ user_pass as sysdba <EOF
Set linesize 800;
Set long 2048576;
Set serveroutput on;
Var oi_return number;
Call $ user_name. $ produre_name ($ statis_sign,: oi_return );
Select: oi_return from dual;
Exit
EOF'
Echo "$ SQL _str" | sed-e '4,/^ $ /! D;/^ $/d' |
While read run_return
Do
Echo $ run_return
Done
2. Call and execute the shell script of the stored procedure (ex_proc.sh)

#! /Bin/bash
Sh ex_produre.sh etl jiangtao pdm_cust_act_behav_base 201003 | grep-v OI_RETURN | grep-v-| while read vi_result
Do
# This date
Echo $ vi_result
If [$ vi_result-eq 0]
Then
Echo "this produre is normal run"
Else
Echo "this produre is not normal run"
Fi
Done

3. Related stored procedures and table creation scripts (this can be used as a template. This is a template of a large company, haha)

A. Stored Procedure (pdm_cust_act_behav_base)

Create or replace procedure pdm_cust_act_behav_base (is_month in varchar2, oi_return out number)
/** HEAD
* @ Name etl. pdm_cust_act_behav_base
* @ Caption ??????????
* @ Type ???
* @ Parameter is_month in varchar2 ??????? YYYYMM
* @ Parameter oi_return out number ????????? 0 ??? -1 ??
* @ Description ??????????
* @ Target etl # tdm_cust_act_behav_base
* @ Source hwmk # tmm_ci_user_basic_m
* @ Middle
* @ Version 1.0
* @ Author
* @ Create-date
* @ TODO?
* @ Version
* @ Mender
* @ Modify_date
* @ Modify_desc
* @ Copyright
*/

--*************************************** **************************************** *
--???? : Etl. pdm_cust_act_behav_base
--???? :??????????
--???? : Is_month -????
--???? : Oi_return -????????? 0 ??? -1 ??
--???? : Hwmk. tmm_ci_user_basic_m
--???? : Etl. tdm_cust_act_behav_base
--???? :
--???? :
--???? :
--???? : V1.0
--???? :
--???? :
--???? :
--???? :
--???? :
--*************************************** **************************************** *

Is

Vs_task_name varchar2 (30 );--????
Vs_table_name varchar2 (30 );--???
Vs_message varchar2 (200 );--????
Vi_task_id integer ;--?? Id
Vi_month integer ;--????
Begin
Vs_task_name: = 'pdm _ cust_act_behav_base ';
Vs_table_name: = 'tdm _ cust_act_behav_base ';

--??????
Etl. ps_log (vs_task_name, vs_table_name, is_month, 1, null, vi_task_id );

--?? :??????????
If (is_month is null) then
Vs_message: = '?????????? ';
Etl. ps_log (null, 3, vs_message, vi_task_id );
Oi_return: =-1;
Return;
End if;

------------------------------------------------------------
--??????
Vi_month: = to_number (is_month );

------------------------------------------------------------
Insert into etl. tdm_cust_act_behav_base
(
Statis_month,
Serv_id
)
Select
Vi_month,
15204669284
From dual
;
Commit;
------------------------------------------------------------
--??????
Etl. ps_log (null, 2, null, vi_task_id );
--????
Oi_return: = 0;
Return;

Exception

When others then
--??????
Vs_message: = substr (sqlerrm, 1,200 );
--????
Rollback;
--??????
Etl. ps_log (null, 3, vs_message, vi_task_id );
--????
Oi_return: =-1;
Return;

End;
/
B. Stored Procedure (ps_log)

Create or replace procedure ps_log
(
Is_task_name in varchar2,
Is_table_name in varchar2,
Is_task_sign in varchar2,
Ii_task_status in integer,
Is_task_log in varchar2,
Oi_task_id in out integer
)

--*************************************** **************************************** *
--???? : Etl. ps_log
--???? : DW ????????
--???? : Is_task_name -????
-- Is_table_name -????
-- Is_task_sign -????, ???????????
-- Ii_task_status -????, 1 ?????, 2 ??????, 3 ??????
-- Is_task_log -????, ???? [???]? [???],
--???? [???]
-- Oi_task_id -?? ID ,??? 2? 3 ??????
-- Ii_rowcount -???
--???? : Oi_task_id -?? ID ,??? 1 ??????
--???? :
--???? : Etl. ts_log
--???? :
--???? :???
--???? : 2010-02-01
--???? : V1.0
--???? :
--???? :
--???? :
--???? :
--???? :????
--*************************************** **************************************** *

Is

Vs_err_msg varchar2 (255 );--??????

Begin

If ii_task_status = 1 then

--????????????
Select etl. seq_dw_log.nextval
Into oi_task_id
From dual;

Insert into etl. ts_log
(
Task_id ,--?? ID
Task_name ,--????
Table_name ,--????
Task_sign ,--????
Start_time ,--??????
End_time ,--?????????????
Task_status ,--????
Task_log --????
)
Values
(
Oi_task_id,
Is_task_name,
Is_table_name,
Is_task_sign,
Sysdate,
Null,
'1 ',
'????? '
);

Elsif ii_task_status = 2 then

--???????????? ID ????????
Update etl. ts_log
Set end_time = sysdate,
Task_status = '2 ',
Task_log = '?????? '
Where task_id = oi_task_id;
Else

--?????????? ID ?????????????
Update etl. ts_log
Set end_time = sysdate,
Task_status = '3 ',
Task_log = substr (is_task_log, 1,200)
Where task_id = oi_task_id;
End if;
Commit;

--????
Return;

Exception

--????
When others then
--??????
Vs_err_msg: = substr (sqlerrm, 1,200 );
--????
Rollback;
--??????
Dbms_output.put_line ('etl. ps_log: '| vs_err_msg );
--????
Return;

End;
/

C. Table creation script:

-- Create sequence
Create sequence SEQ_DW_LOG
Minvalue 1
Max value 999999999999999999999999999
Start with 2731
Increment by 1
Cache 20;
Create table TS_LOG
(
TASK_ID INTEGER,
TASK_NAME VARCHAR2 (30 ),
TABLE_NAME VARCHAR2 (30 ),
TASK_SIGN VARCHAR2 (20 ),
START_TIME DATE,
END_TIME DATE,
TASK_STATUS VARCHAR2 (1 ),
TASK_LOG VARCHAR2 (200 ),
ROWCOUNT NUMBER
);
-- Create table
Create table TDM_CUST_ACT_BEHAV_BASE
(
STATIS_MONTH NUMBER (10 ),
SERV_ID NUMBER (12)
);