OraFAQ Forum: SQL & PL/SQL » ORA-19011: Character string buffer too small

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-19011: Character string buffer too small (PL/SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-19011: Character string buffer too small [message #388686]

Wed, 25 February 2009 22:59

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

Report message to a moderator

Re: ORA-19011: Character string buffer too small [message #388687 is a reply to message #388686]

Wed, 25 February 2009 23:02

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

Report message to a moderator

Re: ORA-19011: Character string buffer too small [message #388688 is a reply to message #388686]

Wed, 25 February 2009 23:03

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: ORA-19011: Character string buffer too small [message #388693 is a reply to message #388688]

Wed, 25 February 2009 23:14

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?

Report message to a moderator

Re: ORA-19011: Character string buffer too small [message #388696 is a reply to message #388693]

Wed, 25 February 2009 23:17

Michel Cadot
Messages: 68764
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: ORA-19011: Character string buffer too small [message #388701 is a reply to message #388696]

Wed, 25 February 2009 23:25

roospm
Messages: 3
Registered: February 2009
Location: South Africa

Junior Member

Thanks very much. I'll try your suggestion.

Report message to a moderator

Previous Topic:	character manipulation
Next Topic:	SMTP

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 03 01:18:58 CDT 2025