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

2
votes

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.

1
oracleplsqlclob
Experiment with LENGTHB() in the WHERE clause - that will give length in bytes. – mathguy
lengthb("clob") is not allowed. Or did I get this wrong? – Christian4145
Ha, ha. I just checked the documentation, and you are almost right. LENGTHB() can be used with LOB, but only with single-byte ones. So, as applied to CLOB, it is completely useless; it can be used on a single-byte CLOB, but then it's the same as LENGTH(). – mathguy
It depends on the character-set: With lenthb on my clob I get an error: ORA-22998: CLOB or NCLOB in multibyte character set not supported – Christian4145
Right: LENGTHB works only on single-character CLOBs (in which case, obviously, it's the same as LENGTH). According to AskTOM, there is no function in Oracle to give you the length IN BYTES of a CLOB in a multi-byte character set. asktom.oracle.com/pls/apex/… – mathguy

1 Answers

2
votes

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.