Shell calls the Stored Procedure

#! /Bin/sh
##--------------------------------------------------------
##
# -- Function Description: Call Sp (only SP has 0-4 in parameters and 2 out parameters)
# What needs to be improved: parameter type determination
# -- Input parameters:
# -- $1: Database Name (required)
# -- $2: database username (required)
# -- $3: Database User Password (required)
# -- $4: Database SP name (required)
# -- $5: SP input parameters (optional) -- $8: SP input parameters (optional)
# -- Return parameter description:
# -- P_out_sqlcode: Error code for execution of stored procedures ()
# -- P_out_errmsg: Description of the stored procedure execution error ()
# -- P_errmsg: program error description
##--------------------------------------------------------
Sqllogfile = sqllog. tmp
Dbname = $1
Dbuser = $2
Dbpwd = $3
Spname = $4
Param1 = $5
Param2 = $6
Param3 = $7
Param4 = $8
Paramnum =$ ## Number of SP Parameters
If [$ #-lt 4]
Then
Echo "Enter at least four parameters! "
Exit
Fi
Sqlplus-S $ dbuser/$ dbpwd @ $ dbname >$ {sqllogfile} <!
Set serveroutput on size 100000
Declare
P_out_sqlcode number;
P_out_errmsg varchar2 (2000 );
V_ SQL varchar2 (2000 );
V_num number; -- number of in parameters passed by Shell
V_sp_num number; -- number of in parameters of SP
Begin
Select '(' | wmsys. wm_concat (': P' | rownum) |') 'into v_ SQL from dual
Connect by level <= $ paramnum-4 + 2;
V_ SQL: = 'call' | '$ spname' | v_ SQL;
V_num: $ paramnum-4;
Select count (1) into v_sp_num from user_arguments
Where object_name = upper ('$ spname ')
And in_out = 'in ';
If v_num! = V_sp_num
Then
Raise_application_error (-20001, 'P _ errmsg ^ The number of input parameters has an error! ');
End if;

If v_num = '0'
Then
Execute immediate v_ SQL
Using out p_out_sqlcode, out p_out_errmsg;
Elsif v_num = '1'
Then
Execute immediate v_ SQL
Using in '$ param1 ',
Out p_out_sqlcode, out p_out_errmsg;

Elsif v_num = '2'
Then
Execute immediate v_ SQL
Using in '$ param1', in' $ param2 ',
Out p_out_sqlcode, out p_out_errmsg;

Elsif v_num = '3'
Then
Execute immediate v_ SQL
Using in '$ param1', in' $ param2', in '$ param3 ',
Out p_out_sqlcode, out p_out_errmsg;

Elsif v_num = '4'
Then
Execute immediate v_ SQL
Using in '$ param1', in' $ param2', in '$ param3', in' $ param4 ',
Out p_out_sqlcode, out p_out_errmsg;
Else
Raise_application_error (-20001, 'P _ errmsg ^ The number of input parameters has an error! ');
End if;
Dbms_output.put ('P _ out_sqlcode ^ ');
Dbms_output.put_line (p_out_sqlcode );
Dbms_output.put ('P _ out_errmsg ^ ');
Dbms_output.put_line (p_out_errmsg );
End;

/
!

# Get p_out_sqlcode
P_out_sqlcode = $ (awk-F "^" '/^ p_out_sqlcode/{print $2}' $ {sqllogfile })
P_out_errmsg = $ (awk-F "^" '/^ p_out_errmsg/{print $2}' $ {sqllogfile })
P_errmsg = $ (awk-F "^" '{if ($1 ~ "P_errmsg") Print $2} '$ {sqllogfile })
Rm sqllog. tmp
Echo $ p_out_sqlcode
Echo $ p_out_errmsg
Echo $ p_errmsg