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