在从Shell脚本运行sqlplus时管理错误处理

Question

#!/bin/sh

echo "Please enter evaluate database username"
read eval_user
echo "Please enter evaluate database password"
read eval_pass
echo "Please enter the database name"
read db_name

LOGFILE=shell_log.txt

$ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF>> ${LOGFILE}
connect $eval_user/$eval_pass@$db_name
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
DBMS_OUTPUT.put_line('Connected to db');
EOF

if [ $? != 0 ]
then 
echo "The upgrade script failed. Please refer to the log results.txt for more information"
echo "Error code $?"
exit 0;
fi

我正在输入垃圾值，试图强制执行此脚本失败。但是，令人恼火的是，它继续前进而没有任何错误代码的提及。这里还需要做什么？

- roymustang86

---

您正在哪个操作系统用户账户下运行？脚本登录到哪个数据库？ - APC

我尝试了你的脚本，日志文件捕获了错误代码。这是从日志文件中获取的信息：ERROR: ORA-01017: invalid username/password; logon denied SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored. - Incognito

请问，当您执行脚本时，日志文件中记录了什么内容？ - Incognito

Answer 1

马克斯所说的是正确的。尝试这个修改过的脚本。

#!/bin/sh

echo "Please enter evaluate database username"
read eval_user
echo "Please enter evaluate database password"
read eval_pass
echo "Please enter the database name"
read db_name

LOGFILE=shell_log.txt

sqlplus -s /nolog <<-EOF>> ${LOGFILE}
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
connect $eval_user/$eval_pass@$db_name
DBMS_OUTPUT.put_line('Connected to db');
EOF

sql_return_code=$?

if [ $sql_return_code != 0 ]
then
echo "The upgrade script failed. Please refer to the log results.txt for more information"
echo "Error code $sql_return_code"
exit 0;
fi

请注意使用sql_return_code来捕获SQLPLUS返回代码。

DBMS_OUTPUT语句应该会失败，显示错误信息为"SP2-0734: unknown command beginning..."。你可以在日志文件中找到这个错误信息。

在SQLPLUS 11g中可以使用错误记录功能来捕获sp2错误。请参阅http://tkyte.blogspot.co.uk/2010/04/new-thing-about-sqlplus.html以获取更多信息。

- Aji Mathew

---

我只收到错误代码122，而不是实际的数据库错误消息。你知道如何捕获输出吗？ - roymustang86

错误消息被重定向到“shell_log.txt”文件。请在当前目录中查看该文件。如果您想在shell脚本中捕获输出，则需要删除重定向并将SQLPLUS的输出分配给变量。例如：SQL_RESULT=$(sqlplus -s /nolog << EOF WHENEVER SQLERROR EXIT FAILURE WHENEVER OSERROR EXIT FAILURE SET SERVEROUTPUT ON connect $eval_user/$eval_pass@$db_name exec DBMS_OUTPUT.put_line('Connected to db'); exit EOF ) - Aji Mathew

在UNIX操作系统中，Shell脚本可以返回最多255个代码。因此，我不明白如何通过“WHENEVER SQLERROR EXIT SQL.SQLCODE;”正确返回超过255的SQL代码？例如ORA-12703字符集转换不受支持。实际上，我刚刚进行了一个测试并运行了一条失败的SQL语句，它返回了ORA-00936：缺少表达式的错误。坏的sqlplus返回了168（！），所以实际的返回代码936被包装在内，只有余数被返回。936％256 = 168。这不是正确的答案。 - Tagar

Answer 2

你的whenever语句可能是在连接到数据库后执行的（因为你后面提到了它们）。请尝试以下代码：

$ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF>> ${LOGFILE}
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
connect $eval_user/$eval_pass@$db_name
DBMS_OUTPUT.put_line('Connected to db');
EOF

- Max

---

我真的无法解析这个句子。 - Maaark

@Maaark，现在试试。我编辑了答案。 - Jason

Answer 3

Aji的回答如下：

WHENEVER SQLERROR EXIT SQL.SQLCODE;

然后使用。

sql_return_code=$?

这种写法是不正确的（或在大多数情况下是不正确的）。具体细节如下。

---

在UNIX操作系统中，Shell脚本可以返回最高达255的代码。例如："ORA-12703字符集转换不受支持"应该返回代码12703，但它无法适配UNIX 8位返回代码。
实际上，我刚刚测试了一下，运行了一个失败的SQL，返回了"ORA-00936：缺失表达式" -
sqlplus返回值为168（！）。
因此，实际返回代码936被包装在256处，只有余数被返回。936%256=168。

在Windows上可能能够正常工作（未经测试），但在UNIX上则不能（如上所述经过测试）。

唯一可靠的机制可能是将结果存储到日志文件中，然后执行类似于

tail -n 25 spool.log | egrep "ORA-" | tail -n 1 | cut -d: -f1 | cut -d- -f2

所以它将会在 spool 日志文件中使用 grep 命令，然后截取最新的 ORA 代码。

- Tagar

---

OP可以直接使用WHENEVER SQLERROR EXIT 10;或类似的语句。但是说得好。SQLCODE可能会模256等于零！ - Jason

Answer 4

您输入虚假值的事实可能仅与登录相关。然后： 使用Shell脚本检查数据库连接 WHENEVER ...用于SQL脚本执行期间出现错误。一旦您成功连接到脚本（我认为这是您现在的问题），您应该得到由WHENEVER ERROR管理的错误类型，因为您在带有DBMS_OUTPUT的行中忘记了EXEC。

- Plouf

Answer 5

你只能捕获 SQL 错误或操作系统错误。dbms_output 会在 sqlplus 级别本身失败，因此 whenever error 设置不会影响它。

- rajesh