ORA-06502 with varchar2 conversion from clob

0
votes

I experimented a little bit with clobs and varchar2. The thing is that we have a lot of XML in a database table and I wanted to download the XML as fast as possible. As I need to use the C# DevArt components I tried it various ways and found out that clobs (they involve one roundtrip to the database per row) are a lot slower than varchar2.

Thus I am right now downloading the data in two steps. First as much as I can as varchar2 (4000 Byte limit) and then everything else as clob.

Now the tricky part. I came up with the following query to find out what I can retrieve as varchar2:

 select c.xml.getstringval() from customers c where 
 length(t.xml.getclobval()) = length(dbms_lob.substr(c.xml.getclobval(), 4000, 1))

The query is based on the assumption that length is returning the number of characters for clobs and substr is returning the a string with a certain number of bytes. It doesn't work only with length because then I run into trouble with multibytecharacters like à é oder è. But now I have another very strange behaviour. The query above works for most of the rows but for some of them it still throws an ORA-06502. But if I change the limit to 3992 it always works??? (as information, we also store a thumbnail of a picture as Base64 encoded string in the xml).

Does anyone now why the query doesn't work in all cases. Or does anyone have a suggestion how to write a query that find out if a clob can be retrieved as a varchar2?

1
oracleclobvarchar2ora-06502

1 Answers

0
votes

substr is returning the a string with a certain number of bytes

The Oracle documentation says that the amount parameter of SUBSTR function means number of characters (not bytes) to be read for CLOBs.

It doesn't work only with length because then I run into trouble with multibytecharacters like à é oder è

Try using the "Unicode=true;" connection string parameter.

The query above works for most of the rows but for some of them it still throws an ORA-06502. But if I change the limit to 3992 it always works???

Please send us a small test project so that we can reproduce the issue in our environment. Also specify:

  • your connection string (roughly, without credentials)
  • the DDL/DML script of the corresponding database objects
  • the version, NLS_LANGUAGE, NLS_CHARACTERSET, and NLS_NCHAR_CHARACTERSET parameters of your Oracle server