Home » Infrastructure » Unix » call a stored procedure with parameters from shell script
call a stored procedure with parameters from shell script [message #238130] Wed, 16 May 2007 15:59 Go to next message
oraanu
Messages: 22
Registered: September 2005
Location: Boston
Junior Member
Hi,

I need to call a procedure with parameters in my shell script.

I have created a local function in my unix shell script.

unset -f TruncCommonTbls
function TruncCommonTbls
#**********************************************************************
# Purpose:
# To call the sql to truncate the claim_format,claim_format_info
#**********************************************************************
{
typeset -i tempRC=${ESUCCESS}
typeset thisFunc="TruncCommonTbls "

DisplayFuncStats ${thisFunc} "enter"

#------------------------------------------
# run plsql to load final table
#------------------------------------------
sqlplus -s ${DB_LOGIN} >> ${sqlLogFile} <<-EOF
set heading off
whenever sqlerror exit failure
begin
dbadmin.truncate_table('COMMON','CLAIM_FORMAT');
end;
/
list
EOF
tempRC=${?}
#------------------------------------------
# error handling
#------------------------------------------
egrep -i "ORA-|ERROR" ${sqlLogFile} >/dev/null && \
{
echow "Could not truncate central tables!"
tempRC=${EFAILURE}
} || \
{
echoi "Tables truncated successfully!"
tempRC=${ESUCCESS}
}
} # end TruncCommonTbls

when I run my shell script it is not giving any error but the table is not truncated.

can anyone please help me.

Thanks,
Anu
Re: call a stored procedure with parameters from shell script [message #238133 is a reply to message #238130] Wed, 16 May 2007 16:07 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
As always what version of Oracle to 4 decimal places, please.
How/why do you expect answers when you provide NO clues?
Please remove -s from from sqlplus line & post contents of ${sqlLogFile}
Please post source code for PL/SQL procedure dbadmin.truncate_table
as a new line directly before sqlplus please add
$ env | sort -o /tmp/fubar.env
after test run please post contents of /tmp/fubar.env
Previous Topic: Unix and Shell Sctipting
Next Topic: char. position in the text file.
Goto Forum:
  


Current Time: Thu Mar 28 14:45:47 CDT 2024