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

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

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s