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

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.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)