Home » Linux/Unix » How to Call Oracle Stored Procedure from Shell Script?

How to Call Oracle Stored Procedure from Shell Script?

By

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.

See also: