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

Question

I have an oracle function which is given below. when I run this in sql-developer it gives an error

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

However the DBMS_OUTPUT.PUT_LINE(FINAL_RESULT) line in the function is printing the expected output in the output window.

Can anyone help me in this??

create or replace
FUNCTION AVERAGE_WORKFORCE(PERIOD in varchar2, YR in varchar2)  RETURN CLOB AS 
FINAL_RESULT CLOB:=null;
STRING_QUERY_TEXT CLOB:=null;
OUTPUT_RESULT CLOB:=null;
BEGIN
    FINAL_RESULT:='<FINAL_RESULT><HEADER><NAME> </NAME> <NAME>SENIOR DIRECTOR</NAME> <NAME>DIRECTOR</NAME> <NAME>EXECUTIVE</NAME> <NAME>MANAGER</NAME><NAME>CASHIER</NAME><NAME>EMPLOYEE</NAME></HEADER>';    
    STRING_QUERY_TEXT:='SELECT XMLElement("tuple",XMLElement("DESC",''Average number of registered employees''),  XMLElement("PERMANENT_EMP",GET_COUNT_AVERAGE_WORKFORCE('''||PERIOD||''','''||YR||''',''SENIOR DIRECTOR'')),  XMLElement("PERMANENT_EMP",GET_COUNT_AVERAGE_WORKFORCE('''||PERIOD||''','''||YR||''',''DIRECTOR'')),  XMLElement("PERMANENT_EMP",GET_COUNT_AVERAGE_WORKFORCE('''||PERIOD||''','''||YR||''',''MANAGER'')),  XMLElement("PERMANENT_EMP",GET_COUNT_AVERAGE_WORKFORCE('''||PERIOD||''','''||YR||''',''EXECUTIVE'')),  XMLElement("PERMANENT_EMP",GET_COUNT_AVERAGE_WORKFORCE('''||PERIOD||''','''||YR||''',''CASHIER'')),  XMLElement("PERMANENT_EMP",GET_COUNT_AVERAGE_WORKFORCE('''||PERIOD||''','''||YR||''',''EMPLOYEE''))) FROM DUAL';     
    EXECUTE_QUERY_RETURN_RESULT(STRING_QUERY_TEXT,OUTPUT_RESULT);       
    FINAL_RESULT:=FINAL_RESULT||''||OUTPUT_RESULT; 
    FINAL_RESULT:=FINAL_RESULT||''||'</FINAL_RESULT>';
    DBMS_OUTPUT.PUT_LINE(FINAL_RESULT);
    RETURN FINAL_RESULT;
END AVERAGE_WORKFORCE;

i am running this function directly from the sql-developer.. — Andromeda
– Andromeda, Commented Aug 18, 2010 at 7:08
What is the specification of procedure EXECUTE_QUERY_RETURN_RESULT? — Tony Andrews
– Tony Andrews, Commented Aug 18, 2010 at 8:17
I meant, can you post the call you're making to the function - is it a straight select, or an assignment in an anonymous block? It looks like maybe you're doing something like declare x varchar2(255); begin x:= average_workforce(<period>,<year>); end;/ which will give that error if the varchar is smaller than the result. But need to see what you're actually doing. It appears the function is OK so it must be the caller that's complaining? — Alex Poole
– Alex Poole, Commented Aug 18, 2010 at 8:51

JulesLt · Accepted Answer · 2010-08-18 11:00:37Z

6

With apologies to Alex and Tony (who have obviously thought the same thing).

Your code is showing the expected output so the DBMS_OUTPUT line must have succeeded.

DBMS_OUTPUT.PUT_LINE(FINAL_RESULT);

QED the exception is happening after this point. We have one more line in the function.

RETURN FINAL_RESULT;

An educated guess is that the problem is as Alex mentions - the result is too big for the variable defined in the calling code in SQL Developer (i.e. it is not a CLOB, but a string that is too small for the result).

If this is generated code, it may be a bug with SQL Developer and CLOB functions.

Have you tried doing a SELECT function FROM DUAL instead?

edited Aug 18, 2010 at 11:00

answered Aug 18, 2010 at 9:32

JulesLt

1,78510 silver badges10 bronze badges

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

2 Comments

Alex Poole Over a year ago

Apology accepted; wasn't sure if it was insultingly obvious to check what was being done with the returned value <grin>

JulesLt Over a year ago

It may not be obvious when tool generated code fails, especially if it's Oracle's own tool!

baklarz2048 · Accepted Answer · 2010-08-18 07:17:05Z

1

Try this DBMS_OUTPUT.ENABLE(200000);

answered Aug 18, 2010 at 7:17

baklarz2048

11k2 gold badges33 silver badges38 bronze badges

1 Comment

JulesLt Over a year ago

The OP is getting the output in the console, and hitting the DBMS_OUTPUT limit raises it's own exception rather than -06502. I think we have the answer now.

wallyk · Accepted Answer · 2010-08-18 07:09:26Z

0

The easiest fix is to limit the line length to 255 characters by inserting some newlines. See this.

answered Aug 18, 2010 at 7:09

wallyk

58.1k17 gold badges92 silver badges155 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related