oracle 从 shell 脚本运行 sqlplus 时管理错误处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14734131/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Managing error handling while running sqlplus from shell scripts
提问by roymustang86
#!/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
I am entering garbage values trying to force this script to fail. But, annoyingly, it keeps moving ahead without any mention of any error code. What else needs to be done here?
我正在输入垃圾值，试图强制此脚本失败。但是，令人讨厌的是，它继续前进而没有提及任何错误代码。这里还需要做什么？
采纳答案by Aji Mathew
What Max says is correct. Try this modified script
马克思说的是对的。试试这个修改过的脚本
#!/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
Please note the use of sql_return_code to capture the SQLPLUS return code.
请注意使用 sql_return_code 来捕获 SQLPLUS 返回码。
The DBMS_OUTPUT statement should fail with error - "SP2-0734: unknown command beginning...". You can find the error message in log file.
DBMS_OUTPUT 语句应失败并显示错误 - “SP2-0734：未知命令开始...”。您可以在日志文件中找到错误消息。
It is possible to trap the sp2 errors in SQLPLUS 11g using the error logging facility. Please have a look at http://tkyte.blogspot.co.uk/2010/04/new-thing-about-sqlplus.htmlfor more information.
可以使用错误记录工具在 SQLPLUS 11g 中捕获 sp2 错误。请查看http://tkyte.blogspot.co.uk/2010/04/new-thing-about-sqlplus.html了解更多信息。
回答by Max
it might be possible that your whenever statements are executed after connection to the db has been established (since you have mentioned them afterwards). Try the following code :-
有可能在建立与数据库的连接后执行您的 when 语句（因为您之后提到了它们）。尝试以下代码：-
$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
回答by Tagar
Aji's answer with
阿吉的回答
WHENEVER SQLERROR EXIT SQL.SQLCODE;
and then using
然后使用
sql_return_code=$?
is not correct (or not correct in most cases). See details below.
不正确（或在大多数情况下不正确）。请参阅下面的详细信息。
Shell script in an UNIX OS can return codes up to 255.
E.g. "ORA-12703 this character set conversion is not supported" return code should be 12703, but it doesn't fit into UNIX 8-bit return code.
Actually I just did a test and ran a bad SQL that fails with "ORA-00936: missing expression" -
sqlplus returned 168 (!).
So the actual return code 936 was wrapped at 256 and just remainder got returned. 936%256=168.
UNIX 操作系统中的 Shell 脚本最多可以返回 255 个代码。例如“ORA-12703 不支持此字符集转换”返回代码应该是 12703，但它不适合 UNIX 8 位返回代码。
实际上，我只是做了一个测试并运行了一个错误的 SQL，该 SQL 以“ORA-00936：缺少表达式”而失败 -
sqlplus 返回了 168 (!)。
所以实际的返回码 936 被包裹在 256 处，并且只返回了余数。936%256=168。
On Windows this probably could work (not tested), but not on UNIX (tested as explained above).
在 Windows 上，这可能可行（未测试），但在 UNIX 上不可行（如上所述进行测试）。
The only reliable mechanism is probably to spool results into a log file and then do something like
唯一可靠的机制可能是将结果假脱机到日志文件中，然后执行类似的操作
tail -n 25 spool.log | egrep "ORA-" | tail -n 1 | cut -d: -f1 | cut -d- -f2
So it would grep the spool log file and then cut actual latest ORA-code.
所以它会 grep 假脱机日志文件，然后剪切实际的最新 ORA 代码。
回答by Plouf
The fact you are entering fake values, are probably only related to the login. Then: Check database connectivity using Shell script
您输入假值的事实可能仅与登录有关。然后： 使用 Shell 脚本检查数据库连接
The WHENEVER ...are for errors during the SQL script execution. Once you'll successfuly connect with your script (I assume this your problem right now), you should get the kind of error managed by WHENEVER ERRORbecause you forgot the EXECat your line with DBMS_OUTPUT.
的WHENEVER ...是，SQL脚本执行过程中出现错误。一旦你成功连接到你的脚本（我认为这是你现在的问题），你应该得到管理的那种错误，WHENEVER ERROR因为你忘记了EXEC你的DBMS_OUTPUT.
回答by rajesh
You can only trap sql error or os error. The dbms_output will fail at sqlplus level itself so the whenever error setting does not affect it.
您只能捕获 sql 错误或 os 错误。dbms_output 将在 sqlplus 级别本身失败，因此每当错误设置不会影响它。
