在sqlplus中执行时，将plsql错误消息重定向到日志文件 [英] redirect plsql error message to a log file when executing it in sqlplus

问题描述

在sqlplus中执行PL/SQL程序错误消息时，需要一种将PL/SQL程序错误消息重定向到日志文件的方法.

Need a way to redirect PL/SQL program error message to a log file when executing it in sqlplus.

假设PL/SQL程序名为send_2012.sql，并且具有以下异常块

Say the PL/SQL program is named send_2012.sql and it has the following exception block

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;

要在KornShell(ksh)脚本中运行PL/SQL程序，我必须:

To run the PL/SQL program in a KornShell (ksh) script, I have:

sqlplus some_username/'some_password' @some_database \
            @/some/directory/send_2012.sql \
            $parameter1 $paramenter2

假设执行send_2012.sql时发生错误，如何将错误消息从var_err重定向到/some/log/directory/log_send_2012.txt?

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?

非常感谢.

推荐答案

像这样设置脚本:

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

从该目录登录sqlplus并运行:

log into sqlplus from that directory and run:

SQL>@test.sql

您将在日志文件(test.log)中找到例外.

You'll find the exceptions in the log file (test.log).

这篇关于在sqlplus中执行时，将plsql错误消息重定向到日志文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！