ORA-06502 Error

[vrudenko]

Hi all,

When I try executing stored procedure that returns a big number of rows, I get the error:

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

As I understand, I can increase the buffer by changing a parameter in INIT.ORA. IS it right? And what's the parameter?

Any help will be apriciated.

Vira

 

[karluk]

I don't think this error refers to any of the Oracle buffers specified in the init.ora file. Rather it sounds as if you are trying to read char or varchar data into a PL/SQL variable that is too small to hold the entire character string.

Can you give us the line of code that is generating the error? That may provide some clues about what is going wrong.

 

[sem]

You've just seen the case of so called "advanced" Oracle 8 diagnostics. On Oracle 7- this sounds like just "numeric or value error". This message means that the error has occured during string assignment rather then during number assignment or conversion.

 

[carp]

Yes, it IS a much better error message. However, it could be improved.
I have encountered this when I have underestimated the string size that I will be dealing with. For instance, if I declare
my_string VARCHAR2(25);
and then try to load a 30-character string into this variable, I receive this error message. At least now it gives a clue that a character string buffer is too small.

 

[vrudenko]

Hi,

I'd like to explain a litle my problem.
I created a cursor that has a function DEF_COST_CENTRE in select clause.
The error occurs when I try to fetch data into a variable

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "COPYRIGHT.DEF_COST_CENTRE", line 4

Function DEF_COST_CENTRE is very simple and works properly:
(
RESP_NO_V Master_Projects.RESP_NO%TYPE,
Market_Code_V Master_Projects.Market_Code%TYPE) RETURN CHAR
IS
COST_CENTRE_V CHAR(4);
BEGIN
IF RESP_NO_V IS NOT NULL THEN
COST_CENTRE_V := RESP_NO_V;
ELSIF Market_Code_V = '20' THEN
COST_CENTRE_V := '3320';
ELSIF Market_Code_V = '60' THEN
COST_CENTRE_V := '3060';
ELSIF Market_Code_V = '61' THEN
COST_CENTRE_V := '3061';
ELSIF Market_Code_V = '62' THEN
COST_CENTRE_V := '3062';
ELSIF Market_Code_V = '63' THEN
COST_CENTRE_V := '3663';
ELSIF Market_Code_V = '64' THEN
COST_CENTRE_V := '3664';
ELSIF Market_Code_V = '65' THEN
COST_CENTRE_V := '3665';
ELSIF Market_Code_V = '66' THEN
COST_CENTRE_V := '3466';
ELSIF Market_Code_V = '67' THEN
COST_CENTRE_V := '3467';
ELSIF Market_Code_V = '68' THEN
COST_CENTRE_V := '3568';
ELSIF Market_Code_V = '70' THEN
COST_CENTRE_V := '3370';
ELSIF Market_Code_V = '71' THEN
COST_CENTRE_V := '3371';
ELSIF Market_Code_V = '72' THEN
COST_CENTRE_V := '3372';
ELSIF Market_Code_V = '73' THEN
COST_CENTRE_V := '3373';
ELSIF Market_Code_V = '74' THEN
COST_CENTRE_V := '3074';
ELSIF Market_Code_V = '75' THEN
COST_CENTRE_V := '3075';
ELSIF Market_Code_V = '77' THEN
COST_CENTRE_V := '3277';
ELSIF Market_Code_V = '80' THEN
COST_CENTRE_V := '4080';
ELSIF Market_Code_V = '81' THEN
COST_CENTRE_V := '4581';
ELSIF Market_Code_V = '84' THEN
COST_CENTRE_V := '4684';
ELSIF Market_Code_V = '86' THEN
COST_CENTRE_V := '4486';
ELSIF Market_Code_V = '88' THEN
COST_CENTRE_V := '4488';
ELSIF Market_Code_V = '93' THEN COST_CENTRE_V := '4593';
ELSIF Market_Code_V = '94' THEN COST_CENTRE_V := '4094';
ELSIF Market_Code_V = '95' THEN
COST_CENTRE_V := '4095';
ELSIF Market_Code_V = '96' THEN COST_CENTRE_V := '4696';
ELSIF Market_Code_V = '97' THEN COST_CENTRE_V := '4597';
ELSIF Market_Code_V = '98' THEN
COST_CENTRE_V := '98'; ELSE COST_CENTRE_V := Market_Code_V;
END IF;
RETURN COST_CENTRE_V;
END def_cost_centre;

I've tried to use DECODE instead of my function but got the same ORA-06502.
I confused by the fact that my procedure works properly when I recstrict the number of rows (for ex.: rownum <3333).

I hope for your help!

Vira

 

[karluk]

Try running the following sql statements

select max(length(RESP_NO)) from Master_Projects;
select max(length(Market_code)) from Master_Projects;

Most likely one or the other result will be greater than 4. Your error seems to occur when you try to set COST_CENTRE_V to a string greater than four characters long, and the only times you do this are when you set it to RESP_NO_V and Market_Code_V.

I don't find it surprising at all that you can avoid this error by restricting the number of rows. It's very possible that the first 3333 rows all have values that are 4 bytes or less, but row 3334 generates the error because it happens to have a 5 character resp_no.

Is it possible for you to declare COST_CENTRE_V as type Master_Projects.RESP_NO%TYPE, or maybe Master_Projects.Market_Code%TYPE, whichever is longer? Then you wouldn't have to worry about whether it's large enough to hold the string you are assigning.

 

[vrudenko]

Thank you for help.

I checked the length of values of the fields - the max=4.
And the fields in the table CHAR(4).

Any other adeas?

 

[karluk]

I am puzzled why we don't seem to be making any progress.

I would put an exception handler in the function to try and narrow down which row is causing the problem. Something like

exception
when others
dbms_output.put_line(cost_centre_v||' '||
market_code_v||' '||
resp_no_v);

I would also experiment with modifying the function so that cost_centre_v gets set to substr(resp_no_v,1,4) and substr(market_code_v,1,4). You don't think that you have overflow, but the substr should guarantee you get no more than 4 characters.

I suppose it's possible that you've encountered an Oracle bug, but I have been unable to find any documentation of a bug that's similar to yours. If we can't find any actual problem with your function, you may need to contact Oracle support and let them take a look.

 

[sem]

Your problem is in the field type char(4) - fixed length type. Your actual length is LESS then 4. Change type to varchar2(4).

 

[vrudenko]

I really appreciate all!!!

Now it works. I used function arguments in the function body. After assigning them into local variables, the stored procedure executed correctly.

But, I can't understand why it worked with restricted number of rows.