提问人：Melwin r 提问时间：10/27/2023 更新时间：10/27/2023 访问量：37
如何处理PLSQL中的触发错误以继续执行
How to handle trigger errors in PLSQL to continue execution
问：
Procedure proctest
Is
Begin
Begin
Forall arr1.first..arr1.last save bulk exceptions
Insert into table1 (arr1(indx).testdata);
Exception when others then
For i in 1..sql%bulk_exceptions.count loop
Dbms_output.put_line(sql%bulk_exceptions(i).error_index;
End loop;
End;
—other code which i want to try
End;
标题
我正在从数组中批量插入到表中，并通过保存异常和继续过程转义异常。但是，我有一条记录在表 1 中抛出触发器级别错误。这没有被保存例外子句捕获。有什么办法可以解决吗？
答：
1赞
Koen Lostrie
10/27/2023
#1
以下是批量插入期间触发器出错的示例。
- 创建长度介于 1 和 5 之间的随机字符串的表
- 插入具有固定值的记录
- 创建触发器，在该固定值上抛出value_error
- 奔跑
- 请注意，异常同时具有 dml 错误（错误 1 和 3）和触发器异常（错误：2）
koen 19c>CREATE TABLE test_bp_source (c) AS
2 (
3 SELECT dbms_random.string('x',floor(dbms_random.value(1,6))) FROM dual CONNECT BY LEVEL < 10
4* );
Table TEST_BP_SOURCE created.
koen 19c>INSERT INTO test_bp_source (c) VALUES ('KOEN');
1 row inserted.
koen 19c>SELECT * FROM test_bp_source;
C
________
BFL9T
H6
DGI
S
3L3X
O
RK
X8FO0
M
KOEN
10 rows selected.
koen 19c>CREATE TABLE test_bp_target (c VARCHAR2(4));
Table TEST_BP_TARGET created.
koen 19c>CREATE OR REPLACE TRIGGER test_bp_target_bi
BEFORE INSERT ON test_bp_target
FOR EACH ROW
DECLARE
l_ex EXCEPTION;
l_num NUMBER;
BEGIN
IF :new.c = 'KOEN' THEN
-- force value error
l_num := 'x';
END IF;
END;
/
Trigger TEST_BP_TARGET_BI compiled
koen 19c>DECLARE
2 TYPE c_t IS TABLE OF test_bp_source.c%TYPE INDEX BY PLS_INTEGER;
3 l_c c_t;
4 l_error_count NUMBER;
5 ex_dml_errors EXCEPTION;
6 PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
7 BEGIN
8 SELECT c BULK COLLECT into l_c FROM test_bp_source ORDER BY c;
9 FORALL i IN 1 .. l_c.COUNT SAVE EXCEPTIONS
10 INSERT INTO test_bp_target (c) VALUES (l_c(i));
11 EXCEPTION
12 WHEN ex_dml_errors THEN
13 l_error_count := SQL%BULK_EXCEPTIONS.count;
14 DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
15 FOR i IN 1 .. l_error_count LOOP
16 DBMS_OUTPUT.put_line('Error: ' || i ||
17 ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
18 ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
19 END LOOP;
20 END;
21* /
Number of failures: 3
Number of failures: 3
Error: 1 Array Index: 2 Message: ORA-12899: value too large for column (actual: , maximum: )
Error: 2 Array Index: 5 Message: ORA-06502: PL/SQL: numeric or value error
Error: 3 Array Index: 10 Message: ORA-12899: value too large for column (actual: , maximum: )
PL/SQL procedure successfully completed.
koen 19c>
评论