CLOB 4000 chars

Hi,

I seem to have a problem with a CLOB in an ora 9i DB. I have created a table (xml_table) with a CLOB column (xml_msg). I have created a SP which has 2 parameters:
p_id IN number and
p_xml_msg OUT CLOB

The SP does nothing more then

select xml_msg
into p_xml_msg
from xml_table
where id=p_id;

If I call this sp from a vb/ado 2.5 component, I receive an 06502: PL/SQL: numeric or value error: character string buffer too small

I altered the SP:
select substr(xml_msg,1,32767)
into p_xml_msg
from xml_table
where id=p_id;

This works in ADO. I only found that if the CLOB contains more then 4000 chars, I again receive the 06502: PL/SQL: numeric or value error: character string buffer too small error. Even so if i run this PL/SQL script in either sqlplus or TOAD is returns teh error. I tried using dms_lob package (read and substr) but this give me the same error. So it seems ado has nothing to do with it. Somehow the clob gets converted to a varchar2 (max 4000) and therefore it raises an error if there is more to return. I searched someforums and KB's but untill now did not seem to find a solution or bug notification. I just need a way to simply store a xml message which can be up to 32 k. There must be a sound manner (please NO longs!!)

Can I somehow pass the CLOB back without oracle convert it 2 varchar2????

Any help much appreciated, please email me at tillebuorren_at_XXXnet.nl and replace the XXX with zon

Sander Received on Fri Feb 27 2004 - 14:58:31 CST