Home » Infrastructure » Windows » ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #28637] Sun, 11 January 2004 18:39 Go to next message
rita
Messages: 41
Registered: April 1998
Member
Hi I am getting the following error while doing a insert into a table.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Strangely it always stops after 44 rows are inserted. There are about 22000 rows that need to be inserted.

All columns in the table are not null. However, there are no null columns being inserted. The table has huge number of columns about (25), so i was just wondering if that could be a problem. But if i try to catch the exception in the loop(i.e data is being inserted using a cursor) and put a null on exception, it inserts about 7000 records.

Any help would be appreciated.
Thanks
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #28638 is a reply to message #28637] Mon, 12 January 2004 01:11 Go to previous messageGo to next message
etryblr
Messages: 28
Registered: December 2003
Junior Member
just chkout whthr this wud help u

run this b4 u run your PL/SQL

SQL > set serveroutput on buffer 2560000
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #28639 is a reply to message #28637] Mon, 12 January 2004 03:32 Go to previous messageGo to next message
Sud
Messages: 63
Registered: September 2002
Member
It sounds like you are missing quotations in strings or supplying a string value which is not enclosed with balance quotes. Check the values being inserted, especially strings/literals, in the INSERT statement. If you are using any bind variables, you better display the values being inserted using DBMS_OUTPUT.PUT() etc commands in the pl/sql. Remember, when you want to see the values using DBMS_OUTPUT.PUT(), make sure the SERVEROUTPUT is set ON.
If you still have problem and not able to figure it out, please provide here the script or INSERT statement you are using, so that we would be able to look at the scripts and can advise you better. Good luck :)
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #28646 is a reply to message #28637] Mon, 12 January 2004 22:44 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
You get this error when you attempt to assign a value to a PL/SQL variable which is not big enough for it. Note that a failed INSERT gives a different error.
SQL> DECLARE
  2     v_test VARCHAR2(1);
  3  BEGIN
  4     v_test := 'bananas';
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

SQL> CREATE TABLE bananas (name VARCHAR2(1));

Table created.

SQL> INSERT INTO bananas (name) VALUES ('One banana two banana');
INSERT INTO bananas (name) VALUES ('One banana two banana')
                                   *
ERROR at line 1:
ORA-01401: inserted value too large for column
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #28653 is a reply to message #28646] Tue, 13 January 2004 20:53 Go to previous messageGo to next message
rita
Messages: 41
Registered: April 1998
Member
Thanks for the Help.
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #30612 is a reply to message #28653] Mon, 10 May 2004 23:52 Go to previous messageGo to next message
Bui Minh Tuan
Messages: 1
Registered: May 2004
Junior Member
You may not initiate your output parameter.
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #310617 is a reply to message #28637] Tue, 01 April 2008 15:37 Go to previous messageGo to next message
Danielle3
Messages: 7
Registered: July 2007
Junior Member
Hi I am experiencing the same issue, and can not for the life of me figure this out. My procedure larger then the original post so I will attach it as a SQL file.

If anyone can offer any quick advice or a solution that would be awesome as my deadline is tomorrow and I'm tired of wasting time on this.
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #310624 is a reply to message #28637] Tue, 01 April 2008 16:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> I'm tired of wasting time on this.
So why should others, like me, waste their time on solving YOUR problem?
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #310625 is a reply to message #310624] Tue, 01 April 2008 16:23 Go to previous messageGo to next message
Danielle3
Messages: 7
Registered: July 2007
Junior Member
Someone is touchy.

Do not read to much into that comment. I was saying that I'm tired of wasting my time as I obvisiouly can not figure out a solution and need some help from others at this point.
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #310633 is a reply to message #28637] Tue, 01 April 2008 19:29 Go to previous messageGo to next message
Danielle3
Messages: 7
Registered: July 2007
Junior Member
I was able to find the issue and resolve it. Razz

It was indeed a variable that wasn't large enough to accept the returned value of a query.

Whatever happened to Data Quality?

Thank you for the encouragement anacedent.

Have a sunny day!
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #341344 is a reply to message #310633] Mon, 18 August 2008 07:38 Go to previous messageGo to next message
sagayakapil
Messages: 1
Registered: August 2008
Location: chennai
Junior Member

Hi there,
me too experienced the same type of error it is because of the data type. when ever we try to assign a velue greater than the actual size of the data type it will through this error.

thanks.
Kapil.A
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #451971 is a reply to message #28637] Mon, 19 April 2010 05:48 Go to previous messageGo to next message
robbie70
Messages: 2
Registered: April 2010
Location: London
Junior Member
Hi Forum,

I've been reading with interest the posts in this thread. I am getting the same error,

Error at line 3
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "R17_AUDIT_REPORT", line 276
ORA-06512: at line 1

I am running a SQL statement, using a Cursor to scroll through the dataset and pass each value from the Cursor into a local variable before spooling out the data to a flat file.

I think one of my local variables is not large enough to contain the data coming back from my SQL/Cursor. The question is how do you best debug this problem?

I have now resorted to putting a DBMS_OUTPUT.PUT_LINE statement in between each of my statements passing the SQL/Cursor value into the local variable to try and trap the problem but there must be a better way? Luckily I only have 10 columns returning from my SQL but another report could be a lot bigger and this way doesnt seem a very neat solution.

Has anyone got any good tips please ?

Thank you in advance.

Rob.
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #451972 is a reply to message #28637] Mon, 19 April 2010 05:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well what does line 276 of R17_AUDIT_REPORT do?
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #451973 is a reply to message #451971] Mon, 19 April 2010 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add the length of your string in a counter variable and display the value at each loop.
One question: why don't you allocate a string large enough for your expected data? If they are data from a table then you know the max size.

Regards
Michel
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #451975 is a reply to message #451973] Mon, 19 April 2010 06:20 Go to previous messageGo to next message
robbie70
Messages: 2
Registered: April 2010
Location: London
Junior Member
Thanks for the quick responses.

Ignore the line reference in the error message. This is refering to error output in an Exception block so its not helpful.

I have defined my variables sizes based on the table column sizes but some of the columns returned from the SQL statement are text statements that are generated from a CASE statement so are not bound to a table/column.

I have managed to debug the problem now using the approach mentioned above, i.e by inserting dbms_output.put_line statements in-between each line that transfers data from the cursor to the
local variable. Its just not a very neat solution, but it got me "out of jail" this time but if the query had more records ie. more than the DBMS utility could display, or if I had more fields then it could have been difficult. My example code is shown below.

Thank you for your help and if you have any neat "Oracle" type tips to solve this problem, please post.

Example,
FOR indx IN 1 .. l_c1_main.COUNT 
LOOP
rec_count := indx;

dbms_output.put_line('----before1----');
r_data_rec.firstName := l_c1_main(indx).FIRST_NAME;
dbms_output.put_line('----before2----');
r_data_rec.lastName := l_c1_main(indx).LAST_NAME;
dbms_output.put_line('----before3----');
r_data_rec.email := l_c1_main(indx).EMAIL;
...... 
END LOOP;

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #451977 is a reply to message #451975] Mon, 19 April 2010 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a PL/SQL debugger like DBMS_DEBUG package.
This is the neat solution.

Regards
Michel
icon7.gif  Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #573014 is a reply to message #28637] Wed, 19 December 2012 21:39 Go to previous messageGo to next message
gbravo2k
Messages: 1
Registered: December 2012
Junior Member
hi everybody !!
I found one trivial thing.. I've a .NET App hitting an Oracle DB, invoking an stored procedure with two OUT params.. I wasted almost whole day trying to resolve this "ORA-06502: PL/SQL: numeric or value error: character string buffer too small"... The fu!c$k#ing error was that i forgot to put the parameter size in .NET:

oParam = New OracleParameter("p_Esquema", OracleDbType.Varchar2)
oParam.Direction = ParameterDirection.Output
oParam.Size = 10
oComm.Parameters.Add(oParam)


I hope this would be useful !!

[mod-edit] font and color removed.

[Updated on: Thu, 20 December 2012 07:55] by Moderator

Report message to a moderator

icon14.gif  Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #584487 is a reply to message #573014] Tue, 14 May 2013 15:16 Go to previous message
Arunok
Messages: 1
Registered: May 2013
Junior Member
Thanks gbravo2k ,

I was facing same issue and your solution resolved the issue.
Previous Topic: Insufficient privileges ... (Oracle database vault)
Next Topic: error regarding oracle connection
Goto Forum:
  


Current Time: Thu Mar 28 10:02:02 CDT 2024