ORA-06502: PL/SQL: 'Numeric or Value Error' When CLOB Convert to VARCHAR2 on a Multibyte D

标签：Convert 8191 CLOB Database 32767 VARCHAR2 offset loop

On a database with multibyte characterset like AL32UTF8 specified for NLS_CHARACTERSET the following error is received when a CLOB which contains more than 8191 characters is assigned to a VARCHAR2 variable.

ORA-06502: PL/SQL: numeric or value error when CLOB convert to VARCHAR2

You can reproduce the error with the below code:

declare
     VARCHAR2_32767     VARCHAR2(32767) := NULL ;
     V_CLOB             CLOB            ;
begin
     for i in 1..32767 loop
        V_CLOB           := V_CLOB || 'A';
     end loop;
     /* The below statement fails if data in clob more than
      8191 characters in multibyte characterset environment */
    VARCHAR2_32767   := V_CLOB ; 
end;

 

Exception Stack:

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 10

CAUSE

Bug 11893621 DBMS_LOB.SUBSTR CAN TRUNCATE DATA OVER 8191 BYTES LONG IN MULTIBYTE CHARACTERSET

SOLUTION

This issue is fixed in version 11.2.0.3.0 or later. For earlier versions, download and apply the Patch 9020537 for your version and platform.

Or

To workaround this situation you can programmatically read 8191 characters sequentially and append them to the VARCHAR2 variable using DBMS_LOB.READ function.

declare
     VARCHAR2_32767     VARCHAR2(32767) := NULL ;
     V_CLOB                     CLOB ;
     buffer varchar2(8191):= null;
     amount number :=8191;
     offset number :=1;
     length number;
     
  begin
     for i in 1..32767 loop
        V_CLOB := V_CLOB || 'A';
     end loop;

     length := dbms_lob.getlength(v_clob);

while offset < length loop
       dbms_lob.read(v_clob, amount, offset, buffer);
       VARCHAR2_32767 := VARCHAR2_32767||buffer;
       offset := offset + amount;
end loop;
     
end;
/

 

标签：Convert,8191,CLOB,Database,32767,VARCHAR2,offset,loop
From： https://www.cnblogs.com/feiyun8616/p/17054356.html