Re: CLOB 4000 chars

Hello Sybrand,

this cannot be the case since the error also pops up if I run a sqlplus query or use toad to run it. No ado involved. I only mentioned ADO to describe how I got to the problem description. So it is to easy to blame it on MS-ORA connection.

If you search for "PL/SQL: numeric or value error: characterstring buffer to small" in combination with "CLOB" or "4000" on any oracle forums you will find that this particular problem is been raised several times and untill now I did not find one of the threads being answered.

Surely there must be a way to simply store some chars in 9i (more then 4 k) without having to go back to longs??

Regards,

Sander

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> schreef in bericht news:5afv30dl8v1b228qsb37i6u7ok0tgh8inv_at_4ax.com...
> 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
Received on Sat Feb 28 2004 - 06:22:45 CST