This post we will examine how to get the parameter to the caller (UNIX). ie OUT Oracle parameter will show in UNIX shell script

For example how to find total number of employees and display in shell script

— Below stored procedure returns total number of employees
create or replace procedure p_get_count(p_emp_count out integer)
as
begin
select count(*) into p_emp_count from emp;
exception
when others then
dbms_output.put_line(sqlerrm);
end p_get_count;

Next constructing the shell script

!/bin/ksh
#Purpose : Get employee count
#Name : get_count.sh
#Argument : p_emp_count(integer) as return variable

export DB_USERID=”SCOTT”
export DB_PWD=”TIGER”
export DB_SID=”ORCL”
ORACONN=”$DB_USERID/$DB_PWD@$DB_SID”

# Main Section
echo “program started…”
db_output=`sqlplus -s $ORACONN<<END
SET SERVEROUTPUT ON
declare
n_count integer;
begin
p_get_count(n_count);
dbms_output.put_line(n_count);
end;
/
exit;
END`
echo “Employee count=$db_output”
echo “program ended…”

Run the shell script get_count.sh

sqlandplsql$ sh get_count.sh
program started…
Employee count=8

PL/SQL procedure successfully completed.
program ended…
sqlandplsql$

Total there are 8 employees

Things to remember

  1. Always use error handling in stored procedures
  2. Add appropriate logs both in PL/SQL and Shell Script
  3. Add proper comments

Note:- Very much simple example have used to explain the fundamentals

Also read

How to call stored procedure with argument(IN parameter) from Unix shell script