In DEPT table we have 5 records and we need to add a new Traveling department based out in Boston

Let us see how we can achieve this through unix shell prompt.

First create a stored procedure to accept two IN arguments and a return flag as OUT parameter

— Below procedure insert new record into dept table
— returns Y is success and N for failure
create or replace procedure p_set_dept
(
deptname in varchar2,
deptlocation in varchar2,
success_flag out varchar2
)
as
n_deptno integer;
begin
— finds max deptno
select nvl(max(deptno),0)+1 into n_deptno from dept;
— insert script
insert into dept values(n_deptno,deptname,deptlocation);
commit;
success_flag := ‘Y’;
exception
when others then
success_flag := ‘N’;
dbms_output.put_line(sqlerrm);
end p_set_dept;

Next to create a shell script to call above procedure

#!/bin/ksh

#Purpose : Add new department
#Name : set_dept.sh
#Argument : p_dname(string) and p_dloc(string)

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

p_dname=$1
p_dloc=$2

function set_dept
{
p_deptname=$1
p_deptloc=$2
db_output=`sqlplus -s $ORACONN<<END
SET SERVEROUTPUT ON
declare
v_success_flag varchar2(1);
begin
p_set_dept(‘$p_deptname’,’$p_deptloc’,v_success_flag);
dbms_output.put_line(v_success_flag);
end;
/
exit;
END`
db_status=`echo $db_output | cut -b 1`
}

# Main Section
echo “program set_dept.sh started…”
set_dept $p_dname $p_dloc
if [ $db_status = “Y” ];
then
echo “Successfully ran set_dept.sh”
else
echo “Error in set_dept.sh –>$db_output”
exit 1
fi
echo “program set_dept.sh ended…”

Now we have both stored procedure and shell script. Let us execute and see

sqlandplsql$ sh set_dept.sh “Travel” “Boston”
program set_dept.sh started…
Successfully ran set_dept.sh
program set_dept.sh ended…

One record got inserted.

Trying to add same department again( Note that deptname column in DEPT table has a unique constraint).
Program correctly captures the error and displays in the console.

sqlandplsql$ sh set_dept.sh “Travel” “Boston”
program set_dept.sh started…
Error in set_dept.sh
Error=ORA-00001: unique constraint (CUPID_DEV.UQ_DEPT) violated
N
PL/SQL procedure successfully completed

Similarly developers can call any type of procedures with different argument types.

Things to remember:-
1. Catch the return type from Oracle to Unix
2. Print the error if any
3. Capture all logs ( note that above program does not have logs )

 

Did find this article useful ? Please comment.