每当SQLERROR永远无法工作时 [英] WHENEVER SQLERROR never works

问题描述

我不知道这是什么原因，我已经在网上花费了数小时试图找出我的问题所在. 我已经检查了官方文档和一些汤姆·凯特(Thomas Kyte)的想法，并且肯定是有关此主题的主题.

我使用以下代码:

.. < <(sqlplus -s /nolog <<EOF
         set errorlogging on
         WHENEVER OSERROR EXIT FAILURE
         WHENEVER SQLERROR EXIT FAILURE
         connect ${use}/${pwd}@${database}
         @$SCRIPTS_PATH/script.sql
         EOF
          )

echo Exited with $?

我模拟了不同的失败场景，例如:

• 使用了错误的用户名
• 在sql squery中出现语法错误

但是这个家伙一直都返回相同的结果:

Exited with 0

我尝试了不同的方式，例如

WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER SQLERROR EXIT FAILURE 

以此类推，但没有一个起作用.

互联网上的大多数人都说这对他们有用. 所以我在这里很困惑...

此功能可能与我的SQL * Plus/Oracle/shell/Unix的版本有关.但是我没有遇到有关此类限制的信息.

我在这里想念什么?

如果任何人都可以分享任何想法，我将不胜感激. 预先谢谢你.

解决方案

请考虑以下通用代码段:

$ cmd1 < <(cmd2) # Or cmd1 <(cmd2)
$ echo $?

此处，$?设置为cmd1的退出状态. cmd2的退出状态已丢失.

在您的情况下，sqlplus恰好是cmd2.因此，该命令的退出状态不会在$?中捕获.

您可以尝试这个；

$ sqlplus ... | cmd1
$ status=(${PIPESTATUS[@]})
$ for i in ${status[@]}; do
>     [ $i -ne 0 ] && echo Exited with $i
> done
$ echo Exited with 0

请注意，如果cmd1是复杂结构(例如while read)，则您在while循环中运行的所有内容都将在子shell&中运行.任何更改的环境(变量/密码)都将丢失.

I don't know what can be a reason and I spent already hours on the Web trying to figure out what's wrong in my case. I've checked official documentation and some thought from Tom Kyte on this and surely topic on stackoverflow about it.

I use following code:

.. < <(sqlplus -s /nolog <<EOF
         set errorlogging on
         WHENEVER OSERROR EXIT FAILURE
         WHENEVER SQLERROR EXIT FAILURE
         connect ${use}/${pwd}@${database}
         @$SCRIPTS_PATH/script.sql
         EOF
          )

echo Exited with $?

I've emulated different failure scenarios such as :

• used a wrong user name
• made syntax error in the sql squery

But this guy all the time returns the same result:

Exited with 0

I've tried different ways, such as

WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER SQLERROR EXIT FAILURE 

and so on but none of them worked.

Most of the people on internet say that this works for them. So I'm getting confused here...

Probably, this feature somehow related with the version of my SQL*Plus / Oracle / shell / Unix. But I didn't encounter information about such restrictions.

What am I missing here?

If anybody can share any ideas I'll be very grateful. Thank you in advance.

解决方案

Consider below generic snippet:

$ cmd1 < <(cmd2) # Or cmd1 <(cmd2)
$ echo $?

Here, $? is set to the exit status of cmd1. The exit status of cmd2 is lost.

In your case, sqlplus happens to be cmd2. So, the exit status of that command is not captured in $?.

You could try this;

$ sqlplus ... | cmd1
$ status=(${PIPESTATUS[@]})
$ for i in ${status[@]}; do
>     [ $i -ne 0 ] && echo Exited with $i
> done
$ echo Exited with 0

Note that if cmd1 is a complex structure (like while read e.g.) any thing you run in that while loop will be run in a subshell & any environment (variables/ pwd) changed will be lost.

这篇关于每当SQLERROR永远无法工作时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！