Re: CLOB 4000 chars

On Fri, 27 Feb 2004 20:58:31 GMT, "Sander" <x_at_x.nl> wrote:

>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
>

Likely this is being caused by Microsoft not supporting Oracle CLOBs in any of their drivers.
You'll need to verify that of course.
The only alternative seems to switch to an Oracle supplied interface. Those support CLOBs

--
Sybrand Bakker, Senior Oracle DBA