Shell Programming and Scripting

Hi ,

i have created an .sh file that has the following code:

#!/bin/ksh
sqlplus -s p1istuat/p1istuat@CWS_IST6 @Procedure_Execute.sql &
sqlplus -s p1istuat/p1istuat@CWS_IST6 << EOF
exit
EOF

The mentioned Procedure_Execute.sql file inside has the following code:

exec TEST;


i tried to run the shell file as "sh filename.sh" its giving me as sqlplus cannot be found...

please let me know any solution for this....

chk whethere oracle is installed in ur unix machine ....

if installled check the path
to check the location of installation
try which command

/home/mkdmt/src/sh: which sqlplus
/opt/dbms01/app/oracle/product/10.2.0.2/bin/sqlplus

Hi ,

Thanks a lot .I have checked for that.Its giving me :

Unknown user: optier

Also i have done saying echo $ ORACLE_HOME seems there is no oracle installed in that machine.

i have changed the entire .sh file to the following :

part_info=`sqlplus p1istuat/p1uatist@CWS_IST6 <<_EOF_
set serveroutput on size 10000
declare
abc integer;
begin
TEST;
dbms_output.put_line('return code is:') ;
end;
/
exit;
_EOF_`

i got the above code from some forum.
In this case how to see the output means how can i view 'part_info'

no reply??????

It's not allowed to bump up questions, please read our rules.

Hi vins,

You are already got the above 90% stuff for executing the Stored Procedure from Shell Script. Why you are repeating the question ?????



RETVAL=`sqlplus -s USERNAME/PASSWORD@DBNAME <<EOF
SET SERVEROUTPUT ON SIZE 100000
Declare
OUT_STATUS NUMBER;
OUT_MSG VARCHAR2(200);
Begin
ODS_SP_REMOVE_PRE_SUB_DUP(OUT_STATUS, OUT_MSG);
dbms_output.put_line ('KeepThis '||OUT_STATUS ||' '||nvl(OUT_MSG,''));
End;
/
SET SERVEROUTPUT OFF
EXIT;
EOF`

X=`echo $RETVAL | grep KeepThis | awk '{print $2}'`
Y=`echo $RETVAL | grep KeepThis | awk '{print $3}'`

echo "Procedure: ODS_SP_REMOVE_PRE_SUB_DUP output is: "
echo "OUT_STATUS= $X"
echo "OUT_MSG= $Y "


Note: Set the Environmentol variables ($ORACLE_HOME, $PATH, $ORACLE_BASE, $ORACLE_SID, $TNS_ADMIN)are must before call the Oracle Stored Procedure.