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
Messages: 22
Registered: September 2005
Location: Boston
Junior Member

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
# error handling
egrep -i "ORA-|ERROR" ${sqlLogFile} >/dev/null && \
echow "Could not truncate central tables!"
} || \
echoi "Tables truncated successfully!"
} # 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.

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
Messages: 24956
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: Fri Oct 21 06:23:33 CDT 2016

Total time taken to generate the page: 0.14465 seconds