Friday 9 October 2015

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind




The example below is raising a ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
  
DECLARE

TYPE rec1_typ IS RECORD (len_3          VARCHAR2(3) ,
                         len_5          VARCHAR2(5)) ;
                     
TYPE rec1 IS TABLE OF rec1_typ INDEX BY BINARY_INTEGER ;    
rec1_tab  rec1 ;                 
      
CURSOR c1
IS    
SELECT 'xxx' a , 
       'xxxxxxxxxxx' b 
  FROM DUAL ;    

BEGIN

   OPEN c1 ;
   FETCH c1 BULK COLLECT INTO rec1_tab ;
   CLOSE c1 ;

END ;
/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 18

The issue is being caused because rec1_type.len5 is defined as a VARCHAR2(5) but the cursor is trying to assign an 11 charater string of 'xxxxxxxxxxx'. In this case the fix is to resize the collection.
DECLARE

TYPE rec1_typ IS RECORD (len_3          VARCHAR2(3) ,
                         len_5          VARCHAR2(11)) ;
                     
TYPE rec1 IS TABLE OF rec1_typ INDEX BY BINARY_INTEGER ;    
rec1_tab  rec1 ;                 
                     
CURSOR c1
IS    
SELECT 'xxx' a , 
       'xxxxxxxxxxx' b 
  FROM DUAL ;    

BEGIN

   OPEN c1 ;
   FETCH c1 BULK COLLECT INTO rec1_tab ;
   CLOSE c1 ;

END ;
/



1 comment: