Take the 2-minute tour ×

Here's the code... this isn't the complete code. I trimmed it down to where the first error occurred:

 FUNCTION get (  
    p_sql_o                         OUT VARCHAR2
  str_sql           VARCHAR2(4000);
    str_sql := ' SELECT * FROM ( SELECT A.*, ROWNUM RNUM FROM ( ' ||
               ' SELECT item_code, ' ||
               ' item_desc, ' ||
               ' monitor, ' ||
               ' measured, ' ||
               ' inventory, ' ||
               ' (measured - inventory) adj_amount, ' ||
               ' (inventory_cost * measured) measured_cost, ' ||
    RETURN str_sql;
  END get;

Obviously, the SQL is incomplete, but I'm not running it. I'm simply returning the SQL string, yet I still get an error:

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

This is baffling. Does anyone have any clue as to why this would be the case?

share|improve this question
Are you getting the error inside the get function? Or are you getting the error in the caller on the line where the get function is called? If the latter, that would imply that the caller hasn't allocated a large enough string for the function to return the data into. Also, it's very odd that you have an OUT parameter in the function that, from the name, seems to be used to return a SQL statement to the caller in addition to the RETURN statement. The OUT parameter here is completely superfluous. –  Justin Cave Feb 27 '13 at 17:40
What is the value of p_sql_o ? –  Hugh Jones Feb 27 '13 at 17:40
I can run this without error... so yes, little strange. You're nowhere near the 4k limit so even if you were running this in a UTF-16 DB the number of bytes wouldn't reach 4k. I suspect you're trying to put the output into a variable that's too small. How are you calling this? –  Ben Feb 27 '13 at 17:40
If I eliminate the last 'inventory' line in the string, there is no error. Error occurs before I even get to the RETURN. –  jlrolin Feb 27 '13 at 17:43

3 Answers 3

up vote 1 down vote accepted

Since you've said you aren't attempting to dynamically execute the SQL you're creating, the syntax and correctness of what's in there, or the length of what it might return, is clearly irrelevant. You aren't doing anything with your p_sql_o OUT parameter, so that isn't causing this problem either. That only really leaves str_sql as the culprit, and as Justin implied yesterday, it's declared as large enough within the function and the function itself compiles OK - so it looks like it has to be how it's being called that's the problem.

You mentioned it works if you remove the 'inventory', which reduced the length of that string from 201 to 192 characters, so I'm guessing you've got this set to 200 chars in the caller, something like:

    str_sql varchar2(200);
    p_sql_o varchar2(4000);
    str_sql := get(p_sql_o);

ERROR at line 1:
ORA-06512: at line 5

Note that the line number this is reporting is 5, not 6 as you had; it's the line in the caller that has the error, not the line the assignment happens inside the function. If I make it str_sql varchar2(250); in the caller declaration then it works (but it might as well be 4000 to match the declaration in the function).

    str_sql varchar2(250);
    p_sql_o varchar2(4000);
    str_sql := get(p_sql_o);

PL/SQL procedure successfully completed.

But p_sql_o will still be empty because you never set it. It looks like the OUT parameter is redundant. Perhaps you meant to put the SQL string into that instead, and that is already declared as big enough in the caller; but then it isn't clear what the return value would be - you probably just want to remove the OUT parameter completely, and make sure the variable in the caller that you're putting the return value into is large enough.

share|improve this answer
The debugger actually set a return to 200 characters. Strangely, when I attempted to call the SQL string from a .NET application... it did the same thing. I'm not exactly sure what the culprit was there, but the debugger actually set a result variable to only 200 characters. Increasing that helped alleviate that issue. –  jlrolin Feb 28 '13 at 13:35

Have you tried just running the SQL script against the DB outside of the SP? This will help to determine if the sql syntax is correct and that you are returning what is expected. It looks like you expect to get a response that is 4000 bytes or smaller. Are you sure that is what is being returned? I don't see Where clause. Maybe you are returning multiple rows that exceed your limit? Admittedly, I'm not as familiar with stored procedures, so I could be off.

share|improve this answer

Here you are using p_sql_o as out parameter and not assigning any value inside your function. So p_sql_o will hold the value it had previously in the code follows after calling line of this function.

As you defined str_sql can hold only 4000 char, if your concatenated string is going beyond that limit it will give character string buffer too small ORA-06512 error.

When you are concatenating the string to create a select statement, check if you are using the single quotes properly or you are missing them at all. If this is happening then while executing the dynamic select statement you may get this error ORA-06502: PL/SQL: numeric or value error:

share|improve this answer

Your Answer


