In this tutorial, I am giving an example to call an Oracle stored procedure from a shell script in Linux.
Calling Oracle PL/SQL Stored Procedure from a Shell Script - Example
The following shell script will call an Oracle stored procedure compute_sal by passing a parameter employee number as l_empno. The variable l_empno will take the first parameter value passed to the shell script. For Oracle database username and password, I used the variables in the shell script; you can use your Linux system shell environment variables instead.
compute_sal.sh
#!/bin/bash l_user='scott' l_psw='tiger' l_conn_string='//YourHost:1521/orcl' # first parameter passed to this shell script l_empno=$1 sql_error=`sqlplus -silent $l_user/[email protected]$l_conn_string <<END WHENEVER SQLERROR EXIT 1 begin /* Call compute_sal stored procedure */ compute_sal($l_empno); exception when others then raise; end; / EXIT; END` Result=$? echo $Result if [[ $Result -ne 0 ]] then echo "${sql_error}" echo "Error calling the PL/SQL procedure." exit $Result else echo "PL/SQL Procedure executed successfully." exit 0 fi
The $? determines the execution status of the last command in Linux, if $? value is other than 0 means the last command execution is failed. The above script is storing the $? status to the variable Result, then echoing it and checking through the "if condition" to determine if the Oracle procedure called successfully.
Test the script by passing employee number 7654
./compute_sal.sh 7654
Output
0 PL/SQL Procedure executed successfully.