OraFAQ Forum: Unix » call a stored procedure with parameters from shell script

Home » Infrastructure » Unix » call a stored procedure with parameters from shell script

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

call a stored procedure with parameters from shell script [message #238130]

Wed, 16 May 2007 15:59

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

Report message to a moderator

Re: call a stored procedure with parameters from shell script [message #238133 is a reply to message #238130]

Wed, 16 May 2007 16:07

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

Report message to a moderator

Previous Topic:	Unix and Shell Sctipting
Next Topic:	char. position in the text file.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 22:57:41 CDT 2025