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

1048939

Hi,

I'm using the CLOB datatype for one of my columns which contains a huge sql statement running into more than five thousand words. When compiling I get no errors, but when running the proc, I get the below errors:

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

ORA-06512: at "RPT_POSITION_G", line 71

ORA-06512: at line 6

line 71, is the place where I initialize the CLOB variable with the sql statement.

Since, the variable is a CLOB variable, I don't understand why it is saying character string buffer is too small.

The version of the Oracle Database is 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production.

Can someone please point me what is going on here?

Thanks.

Paul Horth

Please post code that is causing the problem.

User_4UXVN

the clob variable should be data type of Varchar2(2000).

Try this DBMS_OUTPUT.ENABLE(200000);

1048939

Hi Paul,

Thanks for the response. This is a proprietary production code, so I cannot paste the exact code in full but please note this was working fine till I added some more logic to account for new requirements.

CREATE OR REPLACE PROCEDURE RPT_POSITION_G

(

p_group           IN VARCHAR2,

Cur_Rec         IN OUT SYS_REFCURSOR

)

IS

l_str CLOB;

BEGIN

    l_str := 'huge dynamic sql statement here which runs into more than five thousand words which is derived based on the custom grouping entered by user';

    OPEN Cur_Rec FOR l_str;

END RPT_POSITION_G;

Thanks,

Chris Hunt

Can someone please point me what is going on here?

Sure. I'm pointing at the problem in your code right now.

You should be able to see my finger the same way I can see your code.

RogerT

The Problem is:

 l_str := 'huge dynamic sql statement here which runs into more than five thousand words which is derived based on the custom grouping entered by user';

This                                       ^

is a string literal, which may not exceed 32K, whereas

 l_str := 'huge dynamic sql statement here which runs into more than five thousand words which is derived based on the custom grouping entered by user';

this        ^

would be your clob, where the size is much bigger.

So you have to build your l_str by concatenating chunks of you "huge dynamic sql statement"

hope this helps

Paul Horth

Roger,

That's what I thought too, but then the O/P states it compiled and the error occurred when they ran the procedure.

If it was too big a string literal, it would fail to compile, wouldn't it?

Anyway, worth seeing if your idea fixes the problem.

Etbin

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

declare

  the_sql  clob := 'select sysdate /* '||rpad('a very long comment ... ',32767,'_')||'*/ from dual';

  the_date date;

begin

  dbms_output.put_line('length(the_sql) = '||to_char(length(the_sql)));

  execute immediate the_sql into the_date;

  dbms_output.put_line('sysdate: '||to_char(the_date,'dd-Mon-yyyy hh:mi:ss am'));

end;

length(the_sql) = 32797

sysdate: 05-Nov-2013 11:33:49 am

Statement processed.

declare

  the_sql  clob := 'select sysdate /* '||rpad('a very long comment ... ',32768,'_')||'*/ from dual';

  the_date date;

begin

  dbms_output.put_line('length(the_sql) = '||to_char(length(the_sql)));

  execute immediate the_sql into the_date;

  dbms_output.put_line('sysdate: '||to_char(the_date,'dd-Mon-yyyy hh:mi:ss am'));

end;

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

Maybe not every concatenation was done using clobs as Roger suggests or less likely due to some logic addition (the_sql might be an insert or an update or using sql types).

Regards

Etbin

Karthick2003

If you are building Large SQL string on the fly you can consider using DBMS_SQL. You can use the VARCHAR2A type to store your SQL string.

1048939

Thanks Roger. I split my sql into variables but now I'm getting a new error which is ORA-00907 error which is missing right parenthesis error.

Not sure, why Oracle would treat the sql string in buffer with limit, when its end destination is in a CLOB variable.

Anyhow, this seems pretty frustrating to deal with. Can't paste the code due to some company policy, so basically stuck in the middle of nowhere.

I'm 100% sure there is no issue with code, seems 'some' inherent bug with the right parenthesis error that Oracle is throwing.

1048939

Thanks. Getting a different error now which is missing right parenthesis error. There is no problem with the code.

RogerT

Not sure, why Oracle would treat the sql string in buffer with limit, when its end destination is in a CLOB variable.

Because....any literal included in single quotes is defined to be a varchar.

John Spencer

3fac695d-54e8-4d64-8aae-f6eb189adeea wrote:

Thanks. Getting a different error now which is missing right parenthesis error. There is no problem with the code.

Clearly there is a problem with the code, otherwise you would not be getting an error.  Ther are a number of things that can cause the missing right parenthesis error including missing commas and parentheses.  You need to print out the sql statement you generate so you ca nsee what you are running.

If you run the generated statement in sqlplus you should get an indication where the error is.

John

1048939

Sure, I understand that, but don't you think the compiler should be smart enough with regards to destination type. I mean, if you are assigning something which perfectly fits the destination data type/variable then it seems wrong to throw such error. At least, logically doesn't make sense at all.

Unknown

3fac695d-54e8-4d64-8aae-f6eb189adeea wrote:

Sure, I understand that, but don't you think the compiler should be smart enough with regards to destination type. I mean, if you are assigning something which perfectly fits the destination data type/variable then it seems wrong to throw such error. At least, logically doesn't make sense at all.

So far you haven't posted ANYTHING that has a target of a CLOB. The only thing you posted was a VARCHAR2 that you tried to init with a string literal.