(Oracle PL / 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;
在异常块中是否有任何方法可以确定查询遇到错误的行/值?我希望能够记录它,以便我可以进入并修改/更正导致错误的特定数据值.
使用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.
对于非常大的数据集,您可能不希望炸毁PGA内存,因此在这种情况下一定要使用LIMIT子句.