CODEWITHSUNDEEP
oracle-databaseplsql
Andromeda
Andromeda

Reputation: 12897

numeric or value error in Oracle function

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

Upvotes: 2

Views: 2609

Answers (1)

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

Reputation: 50017

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:

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

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

Upvotes: 2

Related Questions