ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #28637] |
Sun, 11 January 2004 18:39  |
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 #28639 is a reply to message #28637] |
Mon, 12 January 2004 03:32   |
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   |
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 #451971 is a reply to message #28637] |
Mon, 19 April 2010 05:48   |
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 #451975 is a reply to message #451973] |
Mon, 19 April 2010 06:20   |
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 #573014 is a reply to message #28637] |
Wed, 19 December 2012 21:39   |
 |
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
|
|
|
|