Search code examples
oracleplsqlclob

DBMS_LOB.SUBSTR with filter on length(CLOB) results in ORA-06502

How can this raise an error?

select 
   DBMS_LOB.SUBSTR("CLOB-Data-Column",4000,1) as column_name 
from "Table_name"
where LENGTH("CLOB-Data-Column") <= 4000

the error is

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

There is no error when I try

select 
   SUBSTR("CLOB-Data-Column",1, 4000) as column_name 
from "Table_name"
where LENGTH("CLOB-Data-Column") <= 4000

but this is much too slow.

Acording to older questions on similar topic on SO and reference on Oracle documentation a string-length of 4000 should be fine for DBMS_LOB.SUBSTR(). The number 4000 means the number of characters for CLOB (according to Oracle refrence). If I change the length of the filter something below 2000 the query would run without errors.

The database column is of the type CLOB. Oracle documentation says for 'amount' (the first argument of DBMS_LOB.SUBSTR): Number of bytes (for BLOBs) or characters (for CLOBs) to be read.

Solution

  • It appears that some characters are multi byte ones. DBMS_LOB.SUBSTR counts characters, and these won't fit into a maximum allowed size of VARCHAR2 which is 4000.