numeric or value error in Oracle function

Question

create or replace
FUNCTION REPORT_GENERATION(  
 IN_STATUS IN VARCHAR2
 ,IN_USERID IN varchar2
)  RETURN CLOB AS 

  FINAL_RESULT CLOB:=null;
  OUTPUT_RESULT CLOB:=null;
 BEGIN
   /* My implementation. OUTPUT_RESULT contains large XML string */   
   FINAL_RESULT:=FINAL_RESULT||''||OUTPUT_RESULT; 
   FINAL_RESULT:=FINAL_RESULT||''||'</EXCEL_MAIN>'; 
   RETURN FINAL_RESULT; 
END REPORT_GENERATION;

When I am executing this function i am getting an error

ORA-06502: PL/SQL: numeric or value error

I am getting that error while returning the FINAL_RESULT. length of FINAL_RESULT is 38123. If i replace FINAL_RESULT with some small string it is working without any issues.

How can i resolve this issue. Please help..

Try using the DBMS_LOB.APPEND function (docs.oracle.com/cd/E11882_01/appdev.112/e25788/…) rather than the || concatenation operator — Ken Keenan
– Ken Keenan, Commented Oct 25, 2012 at 11:53
@KenKeenan I tried DBMS_LOB.APPEND. Still getting the same error — Andromeda
– Andromeda, Commented Oct 25, 2012 at 12:03
@BobJarvis OUTPUT_RESULT is actually inside a loop. everytime it will be around 1000-2000 in length. I am appending it to FINAL_RESULT in each loop and returning FINAL_RESULT in the end — Andromeda
– Andromeda, Commented Oct 25, 2012 at 12:03
@Jasim - I suspect that when you use the string concatenation operator (||) you're forcing the CLOB to be converted to a VARCHAR2, and if FINAL_RESULT has a length of 38123 that will overflow the maximum allowed size of a VARCHAR2 (32767). — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Oct 26, 2012 at 0:14

Bob Jarvis - Слава Україні · Accepted Answer · 2012-10-25 12:31:42Z

2

Try the following:

create or replace
FUNCTION REPORT_GENERATION(  
 IN_STATUS IN VARCHAR2
 ,IN_USERID IN varchar2
)  RETURN CLOB AS 

  FINAL_RESULT  CLOB := EMPTY_CLOB();
  OUTPUT_RESULT CLOB := EMPTY_CLOB();
 BEGIN
   DBMS_LOB.APPEND(FINAL_RESULT, OUTPUT_RESULT); 
   DBMS_LOB.APPEND(FINAL_RESULT, '</EXCEL_MAIN>'); 
   RETURN FINAL_RESULT; 
END REPORT_GENERATION;

A couple notes:

When working with LOB's (CLOB's or BLOB's) you should always initialize them to EMPTY_CLOB or EMPTY_BLOB. Failure to do this will cause a pile of problems. Play with the above code, changing EMPTY_CLOB to NULL to see what happens.
If you've done #1, you can't compare a CLOB or BLOB to NULL, because it's NOT null. Compare an empty CLOB/BLOB to EMPTY_CLOB()/EMPTY_BLOB().

Share and enjoy.

edited Oct 25, 2012 at 12:31

answered Oct 25, 2012 at 12:26

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

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

3 Comments

Bob Jarvis - Слава Україні Over a year ago

Please edit your post to include the entire function in question. From your comment earlier it seems you posted a much-simplified extract. I have a feeling something else is going on that we're not seeing. Thanks.

Bob Jarvis - Слава Україні Over a year ago

One other question: is the "real" function returning a CLOB or is it returning VARCHAR2?

GriffeyDog Over a year ago

Right, if you have that string going into a varchar2 at any point it will fail with any string value over 32767 characters.

Collectives™ on Stack Overflow

numeric or value error in Oracle function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related