oracle 如何确定 PL/SQL 语句中的行/值抛出错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/944916/
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
How to determine row/value throwing error in PL/SQL statement?
提问by JJ.
(Oracle PL/SQL)
(Oracle PL/SQL)
If I have a simple SQL statement that is throwing an error, ie:
如果我有一个抛出错误的简单 SQL 语句，即：
DECLARE
v_sql_errm varchar2(2048);
BEGIN
UPDATE my_table SET my_column = do_something(my_column)
WHERE my_column IS NOT NULL;
EXCEPTION
when others then
-- How can I obtain the row/value causing the error (unknown)?
v_sql_errm := SQLERRM;
insert into log_error (msg) values ('Error updating value (unknown): '||
v_sql_errm);
END;
Is there any way within the exception block to determine the row/value on which the query is encountering an error? I would like to be able to log it so that I can then go in and modify/correct the specific data value causing the error.
在异常块中是否有任何方法可以确定查询遇到错误的行/值？我希望能够记录它，以便我可以进入并修改/更正导致错误的特定数据值。
采纳答案by Rob van Wijk
A solution using the SAVE EXCEPTIONS clause:
使用 SAVE EXCEPTIONS 子句的解决方案：
SQL> create table my_table (my_column)
2 as
3 select level from dual connect by level <= 9
4 /
Table created.
SQL> create function do_something
2 ( p_my_column in my_table.my_column%type
3 ) return my_table.my_column%type
4 is
5 begin
6 return 10 + p_my_column;
7 end;
8 /
Function created.
SQL> alter table my_table add check (my_column not in (12,14))
2 /
Table altered.
SQL> declare
2 e_forall_error exception;
3 pragma exception_init(e_forall_error,-24381)
4 ;
5 type t_my_columns is table of my_table.my_column%type;
6 a_my_columns t_my_columns := t_my_columns()
7 ;
8 begin
9 select my_column
10 bulk collect into a_my_columns
11 from my_table
12 ;
13 forall i in 1..a_my_columns.count save exceptions
14 update my_table
15 set my_column = do_something(a_my_columns(i))
16 where my_column = a_my_columns(i)
17 ;
18 exception
19 when e_forall_error then
20 for i in 1..sql%bulk_exceptions.count
21 loop
22 dbms_output.put_line(a_my_columns(sql%bulk_exceptions(i).error_index));
23 end loop;
24 end;
25 /
2
4
PL/SQL procedure successfully completed.
For very large data sets, you probably don't want to blow up your PGA memory, so be sure to use the LIMIT clause in that case.
对于非常大的数据集，您可能不想炸毁 PGA 内存，因此在这种情况下一定要使用 LIMIT 子句。
回答by Rob van Wijk
This can be done using DML error logging, if you are on 10gR2 or later.
如果您使用的是 10gR2 或更高版本，则可以使用 DML 错误日志记录来完成此操作。
An example:
一个例子：
SQL> create table my_table (my_column)
2 as
3 select level from dual connect by level <= 9
4 /
Tabel is aangemaakt.
SQL> create function do_something
2 ( p_my_column in my_table.my_column%type
3 ) return my_table.my_column%type
4 is
5 begin
6 return 10 + p_my_column;
7 end;
8 /
Functie is aangemaakt.
SQL> alter table my_table add check (my_column not in (12,14))
2 /
Tabel is gewijzigd.
SQL> exec dbms_errlog.create_error_log('my_table')
PL/SQL-procedure is geslaagd.
This creates an error logging table called err$_my_table. This table is filled by adding a log errors clause to your update statement:
这将创建一个名为err$_my_table的错误日志记录表。通过向更新语句添加日志错误子句来填充此表：
SQL> begin
2 update my_table
3 set my_column = do_something(my_column)
4 where my_column is not null
5 log errors reject limit unlimited
6 ;
7 end;
8 /
PL/SQL-procedure is geslaagd.
SQL> select * from err$_my_table
2 /
ORA_ERR_NUMBER$
--------------------------------------
ORA_ERR_MESG$
--------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------
MY_COLUMN
--------------------------------------------------------------------
2290
ORA-02290: check constraint (RWK.SYS_C00110133) violated
AAGY/aAAQAABevcAAB
U
12
2290
ORA-02290: check constraint (RWK.SYS_C00110133) violated
AAGY/aAAQAABevcAAD
U
14
2 rijen zijn geselecteerd.
Prior to 10gR2, you can use the SAVE EXCEPTIONS clause: http://rwijk.blogspot.com/2007/11/save-exceptions.html
在 10gR2 之前，您可以使用 SAVE EXCEPTIONS 子句：http: //rwijk.blogspot.com/2007/11/save-exceptions.html
回答by northpole
try outputting your error and see if it gives you the information you are looking for. For example:
尝试输出您的错误，看看它是否为您提供了您正在寻找的信息。例如：
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
回答by Adam Paynter
For more detailing information regarding how execution arrived at the line in question, you could try displaying the output returned by these functions:
有关执行如何到达相关行的更多详细信息，您可以尝试显示这些函数返回的输出：
DBMS_UTILITY.format_error_stack:Format the current error stack. This can be used in exception handlers to look at the full error stack.
DBMS_UTILITY.format_error_backtrace:Format the backtrace from the point of the current error to the exception handler where the error has been caught. NULL string is returned if no error is currently being handled.
DBMS_UTILITY.format_error_stack：格式化当前错误堆栈。这可用于异常处理程序以查看完整的错误堆栈。
DBMS_UTILITY.format_error_backtrace：格式化从当前错误点到错误已被捕获的异常处理程序的回溯。如果当前没有处理错误，则返回 NULL 字符串。
回答by Christian
Try this (not tested):
试试这个（未测试）：
DECLARE
cursor c1 is
select key_column, my_column
from my_table
WHERE my_column IS NOT NULL
ORDER BY key_column;
my_table_rec my_table%ROWTYPE;
BEGIN
FOR my_table_rec in c1
LOOP
UPDATE my_table SET my_column = do_something(my_column)
WHERE key_column = my_table_rec.key_column;
END LOOP;
EXCEPTION
when others then
insert into log_error (msg) values ('Error updating key_column: ' || my_table_rec.key_column || ', my_column: ' || my_table_rec.my_column);
END;
回答by Manuel Ferreria
PL/SQL defines 2 global variables to refer to errors:
PL/SQL 定义了 2 个全局变量来引用错误：
SQLERRM : SQL error Message
SQLERRM：SQL 错误消息
SQLERRNO: SQL error Number
SQLERRNO：SQL 错误号
This is readable in the EXCEPTION block in your PL/SQL.
这在 PL/SQL 的 EXCEPTION 块中是可读的。
DECLARE
x number;
BEGIN
SELECT 5/0 INTO x FROM DUAL;
EXCEPTION
WHEN OTHERS THEN:
dbms_output.put_line('Error Message: '||SQLERRM);
dbms_output.put_line('Error Number: '||SQLERRNO);
END;
