Forum Stats

  • 3,727,966 Users
  • 2,245,512 Discussions
  • 7,853,222 Comments

Discussions

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

1048939
1048939 Member Posts: 52
edited November 2013 in SQL & PL/SQL

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.

Rejishal-Oracle

Answers

  • Paul  Horth
    Paul Horth Member Posts: 3,403

    Please post code that is causing the problem.

  • User_4UXVN
    User_4UXVN Member Posts: 23 Employee

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

    Try this DBMS_OUTPUT.ENABLE(200000);

  • 1048939
    1048939 Member Posts: 52

    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
    Chris Hunt Member Posts: 2,066 Gold Trophy
    edited November 2013
    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
    RogerT Member Posts: 1,852 Gold Trophy

    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
    Paul Horth Member Posts: 3,403

    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
    Etbin Member Posts: 8,968
    edited November 2013

    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
    Karthick2003 Member Posts: 13,711 Bronze Badge

    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
    1048939 Member Posts: 52

    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
    1048939 Member Posts: 52

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

  • RogerT
    RogerT Member Posts: 1,852 Gold Trophy
    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
    John Spencer Member Posts: 8,567
    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
    1048939 Member Posts: 52

    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.

  • 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.

This discussion has been closed.