Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Matthew.

Asked: April 18, 2014 - 4:08 pm UTC

Last updated: April 23, 2014 - 9:36 pm UTC

Version: Release 9.2.0.1.0

Viewed 10K+ times! This question is

You Asked

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

and Tom said...

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...)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library