"ORA-19011: Character string buffer too small"

[Karen]

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.

[DA Morgan]

Post by Karen
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.

[Karen]

Thanks Daniel.

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

Best Regards,
Brian

Post by DA Morgan

Post by Karen
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
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.

[t***@mailinator.com]

Try using the XMLTYPE member function getclobval() instead of to_clob.

-TW