oracle 在 sqlplus 中执行时将 plsql 错误消息重定向到日志文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12058020/
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
redirect plsql error message to a log file when executing it in sqlplus
提问by Hank
Need a way to redirect PL/SQL program error message to a log file when executing it in sqlplus.
在 sqlplus 中执行时，需要一种将 PL/SQL 程序错误消息重定向到日志文件的方法。
Say the PL/SQL program is named send_2012.sqland it has the following exception block
假设 PL/SQL 程序被命名send_2012.sql并且它有以下异常块
EXCEPTION
WHEN NO_DATA_FOUND
THEN
var_err := 'Data not found. ';
WHEN OTHERS
THEN
var_err := 'Error in '
|| $$plsql_unit
|| ' | '
|| SQLERRM
|| ' | '
|| 'Details: '
|| DBMS_UTILITY.format_error_backtrace;
END;
To run the PL/SQL program in a KornShell (ksh) script, I have:
要在 KornShell (ksh) 脚本中运行 PL/SQL 程序，我有：
sqlplus some_username/'some_password' @some_database \
@/some/directory/send_2012.sql \
$parameter1 $paramenter2
Suppose error occurs when executing send_2012.sql, how can I redirect the error message from var_err to /some/log/directory/log_send_2012.txt?
假设执行时发生错误send_2012.sql，如何将错误消息从 var_err 重定向到/some/log/directory/log_send_2012.txt？
Much appreciated.
非常感激。
回答by tbone
Setup your script like this:
像这样设置你的脚本：
-- test.sql script run from sqlplus
set serveroutput on
set echo on
WHENEVER SQLERROR EXIT SQL.SQLCODE
spool on
spool test.log
declare
l_val date;
begin
select sysdate into l_val from dual where 1=0;
exception
when others then raise;
end;
/
spool off
log into sqlplus from that directory and run:
从该目录登录 sqlplus 并运行：
SQL>@test.sql
You'll find the exceptions in the log file (test.log).
您将在日志文件 (test.log) 中找到异常。
回答by Hank
I worked around the logging issue, here is what I did:
我解决了日志记录问题，这是我所做的：
Within the pl/sql program I inserted DBMS_PUTLINE("error messages goes here, etc");to both the program body and exception sections.
在 pl/sql 程序中，我插入DBMS_PUTLINE("error messages goes here, etc");了程序主体和异常部分。
When calling sqlplus from a Korn shell script, I used a regular output redirect to log pl/sql exceptions:
从 Korn shell 脚本调用 sqlplus 时，我使用常规输出重定向来记录 pl/sql 异常：
sqlplus some_username/'some_password' @some_database \
@/some/directory/send_2012.sql \
$parameter1 $paramenter2 \
> /some/log/directory/send_2012.log
What I did may not be the best solution. Wrap Spoolbefore and after your pl/sql program may give you more options on formatting, but it may also include the output result (say from the selectstatement) when system executes the program successfully.
我所做的可能不是最好的解决方案。Spool在 pl/sql 程序之前和之后换行可能会为您提供更多关于格式化的选项，但它也可能包括select系统成功执行程序时的输出结果（例如来自语句）。
