topic call Stored procedure from shell script in Operating System - Linux

call Stored procedure from shell script

suchitra — Wed, 10 May 2006 05:47:14 GMT

Hi,

I am not able to call a stored procedure from a shell script. This is my code

#!/bin/csh -f

#env variables
setenv DB_IND COM_DB
set dbuser = `cat /opt_apps/database/dbparam | grep $DB_IND | awk -F: '{print $3}'`
set dbpasswd = `cat /opt_apps/database/dbparam | grep $DB_IND | awk -F: '{print $4}'`
set oraclehome = `cat /opt_apps/database/dbparam | grep $DB_IND | awk -F: '{print $5}'`
set oraclesid=`cat /opt_apps/database/dbparam | grep $DB_IND | awk -F: '{print $6}'`

setenv ORACLE_HOME $oraclehome
setenv ORACLE_SID $oraclesid
setenv ORACLE_BIN $oraclehome/bin
setenv NLS_LANG American_America.UTF8
setenv TNS_ADMIN $oraclehome/network/admin

set ldrHome = `cat /opt_apps/comcat/r_curr/be/loads/load.config | grep FTP_HOME | awk -F= '{print $2}'`/eu_fusion
set ldrData = `cat /opt_apps/comcat/r_curr/be/loads/load.config | grep FTP_HOME | awk -F= '{print $2}'`/eu_fusion

# loging into sqlplus

sqlplus -s $dbuser/$dbpasswd

#Executing for the SP populate_eu_staging

exec be_fusion_orderability.populate_eu_staging

#Calling eu_fusion_spool.sql

@$ldrHome/sql/eu_fusion_spool.sql

#rename the above generated file

mv $ldrData/out/orderability_ecomcat_fusion.txt $ldrData/out/orderability_`date +%y%m%d`_ecomcat_fusion.txt

It stop at loging in to sqlplus .

Can you please tell me whats wrong.

Re: call Stored procedure from shell script

Peter Godron — Wed, 10 May 2006 05:53:28 GMT

Hi,
don't you need somethng like:
sqlplus -s $dbuser/$dbpasswd << .eof
exec be_fusion_orderability.populate_eu_staging
/
@$ldrHome/sql/eu_fusion_spool.sql
exit;
.eof

To prove it take the -s option off sqlplus.

Re: call Stored procedure from shell script

Renda Skandier — Wed, 10 May 2006 06:19:09 GMT

try

sqlplus -s $dbuser@ORACLE_SID/$dbpasswd

Re: call Stored procedure from shell script

Peter Godron — Wed, 17 May 2006 02:39:39 GMT

Suchitra,

can you please update the thread with the solution used, so that we all can learn how to fix the problem.

Please also read:
http://forums1.itrc.hp.com/service/forums/helptips.do?#28