0

Some time ago I wrote a small routine to run some quick n' dirty queries (and with that I mean it is not used for large queries) against an Oracle DB, but also wanted to do it a bit easier to parse errors. Follows:

# Executes the query
#
# Will execute a query contained in the variable named
# in the parameter $4 and store the result in the variable
# named in $5.
# In case of errors (even SQL related) the function should 
# exit with status 1, making it possible to "if execQuery".
# 
# @param    $1 = User
#           $2 = Pasword
#           $3 = Tns Alias
#           $4 = Name of the variable containing the query
#           $5 = Name of the variable to hold the result
#
# @return   query execution status
function execQuery {
    typeset eSQLU=$1
    typeset eSQLP=$2
    typeset eSQLS=$3
    typeset etQUERY=$4
    eval typeset eQUERY=\$$etQUERY
    typeset eQRES=$5
    logMessageFile "DEBUG" "Query: $eQUERY"

    typeset res=$(sqlplus -s $eSQLU/$eSQLP@$eSQLS <<EOF
set echo off newpage 0 space 0 pagesize 0 feed off head off verify off lines 999
WHENEVER SQLERROR EXIT 1
$eQUERY
exit;
EOF
)
    [[ $? -gt 0 ]] && return 1 || eval "$eQRES=\"$res\""

}

The idea of this function is that later I could do something like:

query="select sysdate from dual;"
if execQuery $RAID_APP_PI_USR $RAID_APP_PI_PWD $RAID_APP_PI_SID query result ; then
    echo $result
    logMessageFile "INFO" "Inserts into XX successful."
else
    logMessageFile "ERROR" "Error insertando XXX."
fi

It kinda works... A properly written query will do it fine, and the result variable is all correctly evaluated and all. The problem are the errors. If the query in that example was something like select * potato potato;, It'd still not yield the correct return value thus missing the error test.

I'm not particularly good with sqlplus nor ksh, probably just missing something obvious... Could someone lend me a hand here?

Thanks!

filippo
  • 5,583
  • 13
  • 50
  • 72

1 Answers1

2

I believe $? is returning the exit status of the typeset command, not the sqlplus command.

It may be easier to output the results of your SQLPLUS statement to a file instead of into a variable. Then you could either read that file with grep, looking for an "ORA-" message, or check the exit status variable.

sqlplus -s $eSQLU/$eSQLP@$eSQLS > querylog.tmp <<EOF 
set echo off newpage 0 space 0 pagesize 0 feed off head off verify off lines 999 
WHENEVER SQLERROR EXIT 1 
$eQUERY 
exit; 
EOF

echo $?
N West
  • 6,768
  • 25
  • 40
  • That is actually what I do in another function for lager queries... I'd like to keep that function simple though, really wish I had a way to fetch the actual result of the query without parsing it or using files... It could give me some headaches with concurrence, multiple executions and abnormal interruptions of the scripts (i.e. parsing the wrong file or living temporary files all around). As I said, it does works in a larger function.. just didn't want to have all this hassle in this simpler one.. – filippo Jan 30 '12 at 17:10
  • I don't know if Korn Shell can do what you're trying to do. Bash allows for the exit status from a subshell (command substitution) to be passed back to the main shell if part of an assignment statement, but for KSH, your best bet may be temporary files. You could always just create a guaranteed unique file name for the temporary file, and purge them daily... – N West Jan 30 '12 at 19:01
  • I would be better not to have the userid and password being passed to SQL*Plus - using the `sqlplus /nolog' option and then immediately using 'CONNECT $eSQLU/$eSQLP@$eSQLS` would prevent that. – Adam Musch Jan 30 '12 at 20:41
  • @AdamMusch Hey, thanks for that. That is not really a problem in my particular case, but sure is relevant in general. I might as well update that. – filippo Jan 31 '12 at 11:19
  • @NWest Yeah. I keep googlin' and that answer keep coming up... I'll leave the question open a while more, but if nothing new comes up that will be it. – filippo Jan 31 '12 at 11:21