Ksh函数查询Oracle并返回值

发布于 2025-01-01 11:10:05 字数 1602 浏览 0 评论 0原文

前段时间，我编写了一个小例程来针对 Oracle DB 运行一些快速的脏查询（我的意思是它不用于大型查询），但也希望更容易地解析错误。如下：

# 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\""

}

这个函数的想法是，以后我可以做类似的事情：

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

它有点有效......正确编写的查询会很好地完成它，并且结果变量都被正确评估。问题是错误。如果该示例中的查询类似于 select *potatopotato;，它仍然不会产生正确的返回值，从而错过错误测试。

我对 sqlplus 和 ksh 都不是特别擅长，可能只是错过了一些明显的东西......有人可以帮我一下吗？

谢谢！

原文

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

三五鸿雁 2025-01-08 11:10:05

我相信$?返回 typeset 命令的退出状态，而不是 sqlplus 命令。

将 SQLPLUS 语句的结果输出到文件而不是变量可能更容易。然后您可以使用 grep 读取该文件，查找“ORA-”消息，或者检查退出状态变量。

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 $?

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 $?

回复收藏 0 原文

~没有更多了~