ORA-06502: character string buffer too small. Even though the string size is under declared size limit

Question

 FOR this_loop
     IN (SELECT field_A, field_B
           FROM TABLE_NAME
          WHERE    num = i_num)
  LOOP
     IF this_loop.field_B BETWEEN 1 AND 3
     THEN
        v_A :=  v_A || ' ' || this_loop.field_A;
     ELSIF this_loop.field_B BETWEEN 4 AND 8
     THEN
        v_field_A := v_field_A || ' ' || this_loop.field_A;  -- Error is at this line
     ELSIF this_loop.field_B BETWEEN 9 AND 15
     THEN
        v_B :=  v_B || ' ' || this_loop.field_A;
     END IF;
  END LOOP;

Variable decalared as

v_field_A            VARCHAR2 (100);

What I know -

Variable v_field_A cannot hold value more than 100 characters
The output I get from the SELECT Query doesn't have more than 10 characters.

My question - How is even possible to face this issue of space buffer when the characters are whitin the limit of varchar2 ? I have faced this issue few years back but last time the cause was output of select query. It had more than 100 characters and hence the size issue but this time it is not more than 10 characters. I'm confused. Any help is appreciated

There are over million rows but this happens only with few rows. — Pirate X
– Pirate X, Commented Jan 11, 2016 at 11:28
to be more specific, how many rows does the loop fetch? 10 char * 11 loop iterations == you'll run into the error since you keep concatenating value to the variable on every iteration — Sathyajith Bhat
– Sathyajith Bhat, Commented Jan 11, 2016 at 11:31
why cant you increase the size of v_field_A ? this will resolve the issue — Indiecoder
– Indiecoder, Commented Jan 11, 2016 at 11:42

Lalit Kumar B · Accepted Answer · 2016-01-11 11:34:39Z

6

Variable v_field_A cannot hold value more than 100 characters

Why not? It is very much possible since you are concatenating the variable for each row in the CURSOR FOR LOOP.

For example,

SQL> DECLARE
  2    v_name VARCHAR2(50);
  3  BEGIN
  4    FOR i IN
  5    (SELECT ename FROM emp
  6    )
  7    LOOP
  8      v_name := v_name || i.ename;
  9    END LOOP;
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8

Use DBMS_OUTPUT to see the current size of the variable and the new value being appended.

Let's debug

SQL> DECLARE
  2    v_name VARCHAR2(50);
  3  BEGIN
  4    FOR i IN
  5    (SELECT ename FROM emp
  6    )
  7    LOOP
  8      dbms_output.put_line('Length of new value = '||LENGTH(i.ename));
  9      v_name := v_name || i.ename;
 10      dbms_output.put_line('Length of variable = '||LENGTH(v_name));
 11    END LOOP;
 12  END;
 13  /
Length of new value = 5
Length of variable = 5
Length of new value = 5
Length of variable = 10
Length of new value = 4
Length of variable = 14
Length of new value = 5
Length of variable = 19
Length of new value = 6
Length of variable = 25
Length of new value = 5
Length of variable = 30
Length of new value = 5
Length of variable = 35
Length of new value = 5
Length of variable = 40
Length of new value = 4
Length of variable = 44
Length of new value = 6
Length of variable = 50
Length of new value = 5

Error

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

It is pretty clear, we wanted to concatenate a string with length 5 to the variable declared as max size 50, currently holding a value of size 50. hence, it throws the error ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

edited Jan 11, 2016 at 11:34

answered Jan 11, 2016 at 11:31

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pirate X Over a year ago

Correct me if I'm wrong. if the variable can hold more than 50 or 100 characters. The problem of size shouldn't be there at all, right ?

Lalit Kumar B Over a year ago

@PirateX You are forgetting the fact that you are concatenating the values to the variable in the loop, therefore, at some point, while iterating through the roes, it exceeds the declared size. I have added an example in my answer.

Pirate X Over a year ago

Ok I think i got the idea. It's concatenation, which at some point exceeds the limit of 100 thereby throwing an error. Right ?

Lalit Kumar B Over a year ago

@PirateX Exactly. That is what you see in the DBMS_OUTPUT in the example I have shown you.

Lalit Kumar B Over a year ago

@PirateX Please mark it as answered, would help others too! I see none of your questions have ever been marked as answered. It is a good gesture and suggested by the community to accept the answer which resolves your problem. Good luck!

dsz · Accepted Answer · 2016-01-11 11:35:16Z

0

11 rows * 10 character > 100 characters => error - your concatenating many rows of 10 characters together into a one of three variables. One of them will be growing each time through the loop.

What am I missing?

Also beware of characters versus bytes. In a varchar2, each CHARACTER will likely take 2 bytes.

answered Jan 11, 2016 at 11:35

dsz

5,3321 gold badge46 silver badges39 bronze badges

Comments

Indiecoder · Accepted Answer · 2016-01-11 11:44:54Z

0

Check how many times this condition is being executed and what values are being concatenated. Since this being concatenated multiple number of times there is a possibility that size of v_field_A is going beyond 50 characters

ELSIF this_loop.field_B BETWEEN 4 AND 8
 THEN
    v_field_A := v_field_A || ' ' || this_loop.field_A;

To resolve this you can increase the size of this variable. You can declare a varchar upto 32,767 bytes

answered Jan 11, 2016 at 11:44

Indiecoder

1863 silver badges19 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2016-01-11 12:01:02Z

0

You can do the string concatenation in a SQL query:

SELECT field_A,
       LISTAGG(CASE WHEN field_B BETWEEN 1 AND 3 THEN field_A END, ' ') WITHIN GROUP (ORDER BY field_A) as val1,
       LISTAGG(CASE WHEN field_B BETWEEN 4 AND 8 THEN field_A END, ' ') WITHIN GROUP (ORDER BY field_A) as val2,
       LISTAGG(CASE WHEN field_B BETWEEN 9 AND 15 THEN field_A END, ' ') WITHIN GROUP (ORDER BY field_A) as val3
FROM TABLE_NAME
WHERE num = i_num;

This is intended to simplify your code. You are still limited by Oracle string lengths. You can get around the Oracle limit in PL/SQL using a CLOB, but that is unnecessary when the final string is only a few hundred characters.

edited Jan 11, 2016 at 12:01

answered Jan 11, 2016 at 11:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Lalit Kumar B Over a year ago

Even LISTAGG has a SQL limit of 4000.

SilentBison · Accepted Answer · 2018-08-31 19:12:19Z

0

The answer to your question lies in how many times the loop executes and how many times it goes inside IF condition.

EXAMPLE :

Condition : BETWEEN 4 AND 8

this_loop.field_A:= 'test';

Number of times loop executes = 100

Due to CONCATINATION the size will definitely shoot up more than 100 CHAR.

Similar will the case with other LOOP conditions.

Solution : Try using CLOB instead of VARCHAR to eliminate this issue.

Oracle error are very descriptive. If it throws some error it pretty much explains the scenario :P.

edited Aug 31, 2018 at 19:12

SilentBison

34 bronze badges

answered Jan 11, 2016 at 12:31

Avrajit Roy

3,3131 gold badge16 silver badges25 bronze badges

Collectives™ on Stack Overflow

ORA-06502: character string buffer too small. Even though the string size is under declared size limit

5 Answers 5

5 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related