Hi Tom, I've got a procedure that bulk collects a bunch of data from one table and uses that data to update another table. The issue I am having is when an exception occurs, the whole process stops and rollsback with no data being updated. My question is, how do I use Bulk collect to still update the data even when I have an exception?
I have these tables:
create table testa ( tranid number, actiontype varchar2(5), notetext varchar2(4000));
create table testb (tranid number, userref8 varchar2(255));
with this data in them:
insert into testa values ( 408247743, 'Notes','This record will pass.' );
insert into testa values ( 408247744, 'Notes', 'This record will fail. 9¬¬¦ * SAVE N DELAY ');
insert into testb values ( 408247743, null);
insert into testb values ( 408247744, null);
using this procedure:
create or replace
PROCEDURE IMP_SP_UPDATE_USERREF8 AS
TYPE T_TRANID IS TABLE OF NUMBER;
TYPE T_NOTE IS TABLE OF VARCHAR2 (255) ;
V_TRANID T_TRANID := T_TRANID();
V_NOTE T_NOTE := T_NOTE();
V_LIMIT NUMBER := 10000 ;
error_code NUMBER := SQLCODE;
ERROR_MSG VARCHAR2(500):= SQLERRM;
V_ERROR_COUNT NUMBER := 0;
ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(EX_DML_ERRORS, -24381);
CURSOR C_CURSOR IS
SELECT TRANID,SUBSTR(NOTETEXT,1,255) TXT
FROM testa
WHERE ACTIONTYPE = 'Notes'
GROUP BY TRANID,SUBSTR(NOTETEXT,1,255)
ORDER BY TRANID;
BEGIN
OPEN C_CURSOR ;
LOOP
DBMS_OUTPUT.put_line('Starting bulk collect.');
FETCH C_CURSOR BULK COLLECT INTO V_TRANID , V_NOTE LIMIT V_LIMIT ;
FORALL I IN V_TRANID.FIRST..V_TRANID.LAST SAVE EXCEPTIONS
UPDATE testb SET USERREF8 = V_NOTE(I)
WHERE TRANID = V_TRANID(I) ;
EXIT WHEN C_CURSOR%NOTFOUND ;
END LOOP;
CLOSE c_cursor;
EXCEPTION
WHEN ex_dml_errors THEN
DBMS_OUTPUT.put_line('Starting exception.');
v_error_count := SQL%BULK_EXCEPTIONS.count;
DBMS_OUTPUT.put_line('Number of failures: ' || v_error_count);
FOR I IN 1 .. V_ERROR_COUNT LOOP
DBMS_OUTPUT.put_line('Error: '||i||' Array Index: '|| SQL%BULK_EXCEPTIONS(i).error_index||' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
COMMIT;
END;
When I run this, I receive the following error:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at "TRECS.IMP_SP_UPDATE_USERREF8", line 28
ORA-06512: at line 1
06502. 00000 - "PL/SQL: numeric or value error%s"
How can I change this to update the good record even though there is a bad record in the dataset? I had thought that the SAVE EXCEPTIONS option would have handled that. Any help you could provide would be appreciated. Thanks!
Matt
Note: I forgot to pack my disk drive and I don't have my 9i VM with me... I only have 11g and 12c.... and I'm getting a lot of 9i/10g questions all of a sudden!!
see
https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:1422998100346727312 for an example, you have your exception handler in the wrong place for this to work first of all. there should be an exception block just around the FORALL statement.
the way you have it coded - it will jump out of the loop to the exception block on the first error, print out stuff, and then commit. If all records succeed, this procedure would just return - and not have committed. Probably not at all what you intended.
Now, I just compiled and ran your code - and did not observe what you did. Unfortunately, I don't know what line 28 of your code is (I had to reformat just to be able to read it - please do use the CODE button in the future to preserve white space and avoid all of those blank lines......)
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>
so, that shows that it "could work", but it doesn't do what you want - the commit never took place.
I also don't understand this query:
SELECT TRANID,SUBSTR(NOTETEXT,1,255) TXT
FROM testa
WHERE ACTIONTYPE = 'Notes'
GROUP BY TRANID,SUBSTR(NOTETEXT,1,255)
ORDER BY TRANID;
wouldn't tranid be unique? wouldn't grouping by tranid be the same as not grouping by tranid? Anyway, I changed your table to simulate a failure:
ops$tkyte%ORA11GR2> create table testb (tranid number, userref8 varchar2(255) check (length(userref8)<= 24) );
and upon re-running your example from scratch:
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, that shows it could work - but it would stop processing after the first error!!!
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 see 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...)