Oracle 使用批量收集时出错

嗨， Tom ，我有一个过程，它从一个表批量收集大量数据，然后使用这些数据更新另一个表。我遇到的问题是，当出现异常时，整个进程停止并回滚，而没有更新数据。我的问题是，我如何使用批量收集仍然更新数据，即使我有一个异常？

我有这些表格：

创建表testa （ tranid编号， actiontype varchar2(5 ） ,Notetext varchar2(4000)) ；

创建表testb (tranid number,userref8 varchar2(255)) ；

其中包含这些数据：

插入到testa值（ 408247743 ，“注释”，“此记录将通过”。）；
插入testa值（ 408247744，'Notes'，'此记录将失败。9 ¬¬¦ *保存N延迟'）；

插入testb值（ 408247743 ，空） ；
插入testb值（ 408247744 ，空） ；



使用此过程：

创建或替换
程序输入_SP_更新_USRREF8 AS

T_TRAID型为数字表；
T型注释为VARCHAR2 (255)的表；
V_TRANID T_TRAID := T_TRAID() ；
V_注释T_注释：= T_注释（） ；
V_限制编号：= 10000 ；
错误码编号：= SQLCODE ；
错误_MSG VARCHAR2(500):= SQLERRM ；
V错误计数编号：= 0 ；
ex_dml_错误异常；
PRAGMA异常_INIT （ EX_DML_错误， -24381 ） ；


光标C_光标为
选择TRANID, subSTR （注意文字， 1 , 255 ） TXT
从测试
WHERE操作类型='Notes'
按TRANID分组，子STR （注释文本， 1,255 ）
按TNID订购；

开始
打开C_C_CurSOR ；
循环
DBMS_OUTPUT.put_line （'开始批量收集。'） ；
取C_C_CURSOR体积收集到V_TRANID,V_注：极限V_LIMIT ；
对于V_TRANID.B.V_TRANID.L中的所有I ，上次保存异常
更新测试b SETUSREF8 = V_注(I)
其中TRANID=V_TRAID(I) ；
当C_C_CurSOR%未找到时退出；
结束弧线；
关闭c_cursor ；
例外
当ex_dml_错误时，然后
DBMS_OUTPUT.put_line （'启动异常。'） ；
v_错误计数：= SQL%BULK_EXITS.count ；
DBMS_OUTPUT.put_line（'失败次数：'|| v错误计数） ；
我在1。V_错误计数弧线
DBMS_OUTPUT.put_line （'错误：'||i|'数组索引： '|| SQL%BULK_EXUS雷公所(i）。错误索引||'消息：'|| SQLERRM ( -SQL%BULK_EXCionS(i).错误码) ) ；
结束弧线；

提交；
结束；

运行此操作时，收到以下错误：
ORA-06502 : PL/SQL ：数值或数值错误：批量绑定：截断的绑定
ORA-06512 ：第28行“TRECS.IMP_SP_UP_USERREF8”
ORA-06512 ：在第1行
06502. 00000 - "PL/SQL ：数值或值错误%s"

即使在数据集中存在坏记录，我如何更改此值以更新好记录？我原以为保存例外选项可以处理这个问题。如果你能提供任何帮助，我们将不胜感激。谢谢！

马特

注意：我忘了打包我的磁盘驱动器和我没有我的9i VM与我...我只有11克和12克...突然间我收到了很多9i/10g的问题！

明白
https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:1422998100346727312
举个例子，您的异常处理程序位于错误的位置，因此无法首先进行此操作。FORALL语句周围应该有一个异常块。

您的编码方式-它将在第一个错误时跳出循环到异常块，打印出内容，然后提交。如果所有记录都成功，则此过程将返回，但未提交。可能根本不是你想要的。


现在，我只是编译并运行您的代码- -并没有观察您的操作。不幸的是，我不知道你的代码第28行是什么（我必须重新格式化，以便能够阅读-请在以后使用代码按钮，以保留空白，避免所有这些空白行...... ）

ops$tkyte%ORA11GR2> create table testa ( tranid number, actiontype varchar2(5), notetext varchar2(4000));

Table created.

ops$tkyte%ORA11GR2> create table testb (tranid number, userref8 varchar2(255));

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into testa values ( 408247743, 'Notes','This record will pass.' );

1 row created.

ops$tkyte%ORA11GR2> insert into testa values ( 408247744, 'Notes', 'This record will fail. 9¬¬¦ * SAVE N DELAY ');

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into testb values ( 408247743, null);

1 row created.

ops$tkyte%ORA11GR2> insert into testb values ( 408247744, null);

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace
  2  PROCEDURE IMP_SP_UPDATE_USERREF8
  3  AS
  4      TYPE T_TRANID IS TABLE OF NUMBER;
  5      TYPE T_NOTE IS TABLE OF VARCHAR2 (255) ;
  6  
  7      V_TRANID T_TRANID := T_TRANID();
  8      V_NOTE T_NOTE := T_NOTE();
  9      V_LIMIT NUMBER := 10000 ;
 10      error_code NUMBER := SQLCODE;
 11      ERROR_MSG VARCHAR2(500):= SQLERRM;
 12      V_ERROR_COUNT NUMBER := 0;
 13      ex_dml_errors EXCEPTION;
 14      PRAGMA EXCEPTION_INIT(EX_DML_ERRORS, -24381);
 15  
 16      CURSOR C_CURSOR IS
 17          SELECT TRANID,SUBSTR(NOTETEXT,1,255) TXT
 18          FROM testa
 19          WHERE ACTIONTYPE = 'Notes'
 20          GROUP BY TRANID,SUBSTR(NOTETEXT,1,255)
 21          ORDER BY TRANID;
 22  BEGIN
 23  
 24      OPEN C_CURSOR ;
 25  
 26      LOOP
 27          DBMS_OUTPUT.put_line('Starting bulk collect.');
 28  
 29          FETCH C_CURSOR BULK COLLECT INTO V_TRANID , V_NOTE LIMIT V_LIMIT ;
 30  
 31          FORALL I IN V_TRANID.FIRST..V_TRANID.LAST SAVE EXCEPTIONS
 32          UPDATE testb SET USERREF8 = V_NOTE(I)
 33          WHERE TRANID = V_TRANID(I) ;
 34  
 35          EXIT WHEN C_CURSOR%NOTFOUND ;
 36      END LOOP;
 37  
 38      CLOSE c_cursor;
 39  
 40  EXCEPTION
 41  WHEN ex_dml_errors THEN
 42      DBMS_OUTPUT.put_line('Starting exception.');
 43      v_error_count := SQL%BULK_EXCEPTIONS.count;
 44      DBMS_OUTPUT.put_line('Number of failures: ' || v_error_count);
 45      FOR I IN 1 .. V_ERROR_COUNT
 46      LOOP
 47          DBMS_OUTPUT.put_line('Error: '||i||' Array Index: '|| SQL%BULK_EXCEPTIONS(i).error_index||
 48                       ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
 49      END LOOP;
 50      COMMIT;
 51  END;
 52  /

Procedure created.

ops$tkyte%ORA11GR2> exec IMP_SP_UPDATE_USERREF8 ;
Starting bulk collect.

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from testb;

    TRANID
----------
USERREF8
----------------------------------------------------------------------------------------------------
 408247743
This record will pass.

 408247744
This record will fail. 9?????? * SAVE N DELAY


ops$tkyte%ORA11GR2> rollback;

Rollback complete.

ops$tkyte%ORA11GR2> select * from testb;

    TRANID
----------
USERREF8
----------------------------------------------------------------------------------------------------
 408247743


 408247744



ops$tkyte%ORA11GR2> 

所以，这表明它“可以工作”，但它不执行您想要的-提交从未发生。


我也不理解这个查询：

SELECT TRANID,SUBSTR(NOTETEXT,1,255) TXT

FROM testa

WHERE ACTIONTYPE = 'Notes' 

GROUP BY TRANID,SUBSTR(NOTETEXT,1,255)

ORDER BY TRANID;

特劳德不是很独特吗？按trid分组和不按trid分组不一样吗？总之，我换了你的表格来模拟失败：

ops$tkyte%ORA11GR2> create table testb (tranid number, userref8 varchar2(255) check (length(userref8)<= 24) );

重新运行示例：

Procedure created.

ops$tkyte%ORA11GR2> exec IMP_SP_UPDATE_USERREF8 ;
Starting bulk collect.
Starting exception.
Number of failures: 1
Error: 1 Array Index: 2 Message: ORA-02290: check constraint (.) violated

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from testb;

    TRANID
----------
USERREF8
----------------------------------------------------------------------------------------------------
 408247743
This record will pass.

 408247744



ops$tkyte%ORA11GR2> rollback;

Rollback complete.

ops$tkyte%ORA11GR2> select * from testb;

    TRANID
----------
USERREF8
----------------------------------------------------------------------------------------------------
 408247743
This record will pass.

 408247744



ops$tkyte%ORA11GR2> 

所以，这表明它可以工作-但它将停止处理后的第一个错误!! !



so, re-look at the example I linked to, put the exception block in the right place. if you still have an error, you can followup here - but use the CODE button and leave your line numbers on so we can 明白 where the error it (if the error is on the FETCH, rip out everything else... if the error is not in the fetch - get rid of the query and just put data into the array (simplify the test case, get rid of anything not relevant to the example...)