oracle – 如何确定PL / SQL语句中的行/值抛出错误?

前端之家收集整理的这篇文章主要介绍了oracle – 如何确定PL / SQL语句中的行/值抛出错误?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
(Oracle PL / sql)

如果我有一个简单的sql语句抛出错误,即:

  1. DECLARE
  2. v_sql_errm varchar2(2048);
  3. BEGIN
  4. UPDATE my_table SET my_column = do_something(my_column)
  5. WHERE my_column IS NOT NULL;
  6. EXCEPTION
  7. when others then
  8. -- How can I obtain the row/value causing the error (unknown)?
  9. v_sql_errm := sqlERRM;
  10. insert into log_error (msg) values ('Error updating value (unknown): '||
  11. v_sql_errm);
  12. END;

在异常块中是否有任何方法可以确定查询遇到错误的行/值?我希望能够记录它,以便我可以进入并修改/更正导致错误的特定数据值.

使用SAVE EXCEPTIONS子句的解决方案:
  1. sql> create table my_table (my_column)
  2. 2 as
  3. 3 select level from dual connect by level <= 9
  4. 4 /
  5.  
  6. Table created.
  7.  
  8. sql> create function do_something
  9. 2 ( p_my_column in my_table.my_column%type
  10. 3 ) return my_table.my_column%type
  11. 4 is
  12. 5 begin
  13. 6 return 10 + p_my_column;
  14. 7 end;
  15. 8 /
  16.  
  17. Function created.
  18.  
  19. sql> alter table my_table add check (my_column not in (12,14))
  20. 2 /
  21.  
  22. Table altered.
  23.  
  24. sql> declare
  25. 2 e_forall_error exception;
  26. 3 pragma exception_init(e_forall_error,-24381)
  27. 4 ;
  28. 5 type t_my_columns is table of my_table.my_column%type;
  29. 6 a_my_columns t_my_columns := t_my_columns()
  30. 7 ;
  31. 8 begin
  32. 9 select my_column
  33. 10 bulk collect into a_my_columns
  34. 11 from my_table
  35. 12 ;
  36. 13 forall i in 1..a_my_columns.count save exceptions
  37. 14 update my_table
  38. 15 set my_column = do_something(a_my_columns(i))
  39. 16 where my_column = a_my_columns(i)
  40. 17 ;
  41. 18 exception
  42. 19 when e_forall_error then
  43. 20 for i in 1..sql%bulk_exceptions.count
  44. 21 loop
  45. 22 dbms_output.put_line(a_my_columns(sql%bulk_exceptions(i).error_index));
  46. 23 end loop;
  47. 24 end;
  48. 25 /
  49. 2
  50. 4
  51.  
  52. PL/sql procedure successfully completed.

对于非常大的数据集,您可能不希望炸毁PGA内存,因此在这种情况下一定要使用LIMIT子句.

猜你在找的Oracle相关文章