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 ;

ora-00904 xml invalid identifier

If you have a XML column as part of a table, for example:

 DESC tab_with_xml_column
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -------------
 ID                                                             NUMBER
 XML_DATA                                                       XMLTYPE
 CREATED_DATE                                                   DATE

Then when trying to apply XML functionality to that column errors can occur, eg:

SELECT xml_data.EXTRACT('/userdetails/username') FROM tab_with_xml_column
       *
ERROR at line 1:
ORA-00904: "XML_DATA"."EXTRACT": invalid identifier

The solution is to simply enclose the column name within brackets:

SELECT (xml_data).EXTRACT('/userdetails/username') FROM tab_with_xml_column ;

(XML_DATA).EXTRACT('/USERDETAILS/USERNAME')
----------------------------------------------------------------------------------------------------
Fred

Example 2:

SQL > SELECT *
        FROM tab_with_xml_column
       WHERE (xml_data).EXTRACT('/userdetails/username/text()').GETSTRINGVAL()  = 'Fred';

                ID
------------------
XML_DATA
----------------------------------------------------------------------------------------------------
CREATED_D
---------
                 1

   Fred
   pass123

20-SEP-15

This was tested on Oracle 10 and Oracle 11. The table and data used was:

CREATE TABLE tab_with_xml_column
(id               NUMBER ,
 xml_data         XMLTYPE ,
 created_date     DATE
)
/

INSERT INTO tab_with_xml_column
(id ,
 xml_data ,
 created_date)
VALUES 
(1,
 XMLTYPE('
   Fred
   pass123
') ,
SYSDATE) 
/

COMMIT
/