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 ;
/

Oracle, accessing collections in SQL

In Oracle it is possible to access collections within SQL statements.

With the following collection:

CREATE TYPE region_tab AS TABLE OF VARCHAR2(2)
/

And the following table and data

desc regions
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------
 SHORT_CODE                                                     VARCHAR2(2)
 NAME                                                           VARCHAR2(70)


SELECT * from regions ;

SH NAME
-- -----------------------------------------
SE South East
WM West Midlands
SW South West
NE North East

We can write the code below which populates the collection with the short_codes of 'WM' and 'SW' and then use a SQL statement to read the collection and find the corresponding regions.name. This is possible through the use of the collection condition MEMBER OF

DECLARE

CURSOR c1
(pl_region_tab    region_tab)
IS
SELECT name 
  FROM regions
 WHERE short_code MEMBER OF pl_region_tab ;

l_region_tab          region_tab := region_tab ();

BEGIN

   -- add values to collection
   l_region_tab.EXTEND ;
   l_region_tab(1) := 'WM' ;
    
   l_region_tab.EXTEND ;
   l_region_tab(2) := 'SW' ;    
    
      FOR c1_rec IN c1 (l_region_tab)
      LOOP
         dbms_output.put_line ('Region name : ' || c1_rec.name) ;
      END LOOP ;      

END ;
/

Region name : West Midlands
Region name : South West

PL/SQL procedure successfully completed.

For completeness the ddl / dml for the table is:

CREATE TABLE regions
(short_code       VARCHAR2(2),
 name             VARCHAR(70)
)
/

INSERT INTO regions (short_code, name) VALUES ('SE', 'South East') ;
INSERT INTO regions (short_code, name) VALUES ('WM', 'West Midlands') ;
INSERT INTO regions (short_code, name) VALUES ('SW', 'South West') ;
INSERT INTO regions (short_code, name) VALUES ('NE', 'North East') ;
COMMIT ;