Re: "ORA-19011: Character string buffer too small"

Thanks Daniel.

Who the heck is Karen...my gmail must be slightly broken..lol.

Best Regards,
Brian

DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1112373116.686799_at_yasure>...
> Karen wrote:
> > Hi,
> >
> > Within a Pl/SQL package I have a large insert statement that returns a
> > "ORA-19011: Character string buffer too small" when it try to run it.
> >
> > The target column is a clob. Here's the statement with many of the
> > columns removed for readablity sake.
> >
> > INSERT INTO t_staging(temp_xml) (SELECT '<VEHICLES>' ||
> > XMLAGG(XMLELEMENT ("VEHICLE",
> > xmlattributes(
> > AB_RG,AB_RG_001,
> >
> > <snipped>
> >
> > VISUAL_ID,WHEEL_DRIVE,
> > XSTATUS))) || '</VEHICLES>' AS "ROW" from policy_vehicle
> > where policy_num = (select distinct policy_num from policy
> > where policy_number = 3 ))
> >
> > How can I get past this error and get my data into the table? I have
> > tried casting the 3 three sections that are getting concatenated using
> > to_clob() but I still get the same error. If I remove 13 columns from
> > the huge list it will work, but I need all the columns.
> >
> > Thanks.
> >
> > Best Regards,
> > Brian P.
>
> I don't have time to try it right now but my instinct would be to
> first define a variable of type CLOB. Then select into the
> variable. Finally insert into the table.
>
> If that doesn't work, and no one has another suggestion, look at
> the demo at:
> http://www.psoug.org
> click on Morgan's Library
> click on DBMS_SQL
> look at the procedure named "execute_plsql_block"
>
> It demonstrates one way of dealing with CLOBs that might work.
Received on Mon Apr 04 2005 - 08:14:11 CDT