Home » SQL & PL/SQL » SQL & PL/SQL » ORA-19011: Character string buffer too small (PL/SQL)
ORA-19011: Character string buffer too small [message #388686] Wed, 25 February 2009 22:59 Go to next message
roospm
Messages: 3
Registered: February 2009
Location: South Africa
Junior Member
I get this message, say 1 out of 20 times. Unfortunately it is usually very embarrassing, as I try to help others, and usually it is urgent. I include the script (condensed) etc... DOES SOMEONE KNOW, WHY THIS HAPPENS?
+++++++++++++++++++ [the actually simple script]
SQL> !cat viewXML.sql
set serverout on size 1000000
set lines 125
set pages 10000
set wrap on
set verify off
set feed off
alter session set NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS';
select '----------------------- ORDER ACTION: ' || '&1' ||
' -------------------------------------' from dual;
column 4 wrap
column 4 newline
column 5 wrap
column 5 newline
set feed on
select substr(OMS_OAID,1,Cool, DECODE (ACTION_TYPE,
'AT', 'Allocate TSI', 'CT', 'Check TSI',
'RP', 'Request Provisioning', 'QP', 'Query Provisioning',
'CP', 'Cancel Provisioning', 'AS', 'Allocate SN',
'PS', 'Propose SN', 'US', 'Unlock SN',
'NI', 'Network Info', 'NA', 'Negotiate Appointment',
'PB', 'Prepare Booking', 'TM', 'Technical Modification',
'OC', 'Order Complete', 'NM', 'Tasking Non-Migrated',
'**************' ) ||' ________________',
DATE_CREATION,
'***IN_XML : '||XMLELEMENT("Response",IN_XML),
'***OUT_XML: '||XMLELEMENT("Response",OUT_XML)
from t_oms_eai where oms_oaid = '&1'
order by DATE_CREATION;
set feed off
set verify off

SQL>
+++++++++++++++++++ [the database table]
SQL> desc T_OMS_EAI
OMS_OAID VARCHAR2(24)
ACTION_TYPE VARCHAR2(2)
DATE_CREATION DATE
IN_XML XMLTYPE
OUT_XML XMLTYPE
SQL>
+++++++++++++++++++ [the output]
SQL> @viewXML 2546383
'-----------------------ORDERACTION:'||'2546383'||'--------------------------------
-----------------------------------------------------------------------------------
----------------------- ORDER ACTION: 2546383 -------------------------------------
select substr(OMS_OAID,1,Cool, DECODE (ACTION_TYPE,
*
ERROR at line 1: ORA-19011: Character string buffer too small
Re: ORA-19011: Character string buffer too small [message #388687 is a reply to message #388686] Wed, 25 February 2009 23:02 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
@Rospm,


You need to help us by following the Posting Guidelines as stated below.

http://www.orafaq.com/forum/t/88153/0/


Regards,
Hammer
Re: ORA-19011: Character string buffer too small [message #388688 is a reply to message #388686] Wed, 25 February 2009 23:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

What part of "ORA-19011: Character string buffer too small " do you not understand?
Re: ORA-19011: Character string buffer too small [message #388693 is a reply to message #388688] Wed, 25 February 2009 23:14 Go to previous messageGo to next message
roospm
Messages: 3
Registered: February 2009
Location: South Africa
Junior Member
OK. How what do I need to do?
[A] Is there a session variable/s that I can set?
[B] How do I break the returned XML up in smaller pieces?
Re: ORA-19011: Character string buffer too small [message #388696 is a reply to message #388693] Wed, 25 February 2009 23:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-19011: Character string buffer too small
 *Cause:  The string result asked for is too big to return back
 *Action:  Get the result as a lob instead

Regards
Michel
Re: ORA-19011: Character string buffer too small [message #388701 is a reply to message #388696] Wed, 25 February 2009 23:25 Go to previous message
roospm
Messages: 3
Registered: February 2009
Location: South Africa
Junior Member
Thanks very much. I'll try your suggestion.
Previous Topic: character manipulation
Next Topic: SMTP
Goto Forum:
  


Current Time: Sat Dec 03 00:58:18 CST 2016

Total time taken to generate the page: 0.31277 seconds