oracle 错误解决 ORA-06502

ORA-06502: PL/SQL: numeric or value error错误解决

    当执行如下语句时出现“06502数字或值错误”，func_fa_getunororgpath函数主要是将人员组织ID参数转换为人员名称、组织 PATH。

Sql代码

1. select  func_fa_getunororgpath(pc.personorgselect)  from  tbl_pt_createtask pc  where  pc.orderid= '2-090210-008-096'   

select func_fa_getunororgpath(pc.personorgselect) from tbl_pt_createtask pc where pc.orderid='2-090210-008-096'

    查看函数得知该函数返回VARCHAR2（4000）类型，函数代码如下：

Sql代码

1. create   or   replace   function  func_fa_getUNorOrgPath(idOrPath  in  varchar2)  return  string  is   
2.    Result varchar2(4000);  
3.    urserOrOrgId varchar2(2000);  
4. begin   
5.    urserOrOrgId:=idOrPath;  
6.   while urserOrOrgId is   not   null  loop  
7.        ……  ……  
8.        ……  ……  
9.       if tmpStr is   null   then   
10.           Result:=Result ||tmpId ||';' ;  
11.      else   
12.           Result:=Result ||tmpStr ||';' ;        
13.      end  if;         
14.   end  loop;  
15.   return (substr(Result,0,length(Result)-1));  
16. end  func_fa_getUNorOrgPath;  

create or replace function func_fa_getUNorOrgPath(idOrPath in varchar2) return string is
   Result varchar2(4000);
   urserOrOrgId varchar2(2000);
begin
   urserOrOrgId:=idOrPath;
  while urserOrOrgId is not null loop
       ……  ……
       ……  ……
      if tmpStr is null then
          Result:=Result ||tmpId ||';';
     else
          Result:=Result ||tmpStr ||';';      
     end if;       
  end loop;
  return(substr(Result,0,length(Result)-1));
end func_fa_getUNorOrgPath;

    当Result长度超过4000时，再执行Result:=Result ||tmpStr ||';';时即出现该错误，固改为返回CLOB类型，代码如下：

Sql代码

1. create   or   replace   function  func_fa_getUNorOrgPath(idOrPath  in  varchar2)  return  CLOB  is   
2.    Result CLOB;  
3.    urserOrOrgId varchar2(2000);  
4.    urserOrOrgId:=idOrPath;  
5. begin   
6.   if idOrPath is   null   then   
7.      return  empty_clob();    
8.   end  if;  
9.   DBMS_LOB.CREATETEMPORARY(Result, TRUE );   
10.   while urserOrOrgId is   not   null  loop  
11.        ……  ……  
12.         if tmpStr is   null   then   
13.             DBMS_LOB.writeappend(Result,LENGTH(tmpId),tmpId);  
14.             DBMS_LOB.writeappend(Result,LENGTH(';' ), ';' );  
15.         else   
16.             DBMS_LOB.writeappend(Result,LENGTH(tmpStr),tmpStr);  
17.             DBMS_LOB.writeappend(Result,LENGTH(';' ), ';' );  
18.         end  if;    
19.   end  loop;  
20.   return (substr(Result,0,length(Result)-1));  
21. end  func_fa_getUNorOrgPath;  

create or replace function func_fa_getUNorOrgPath(idOrPath in varchar2) return CLOB is
   Result CLOB;
   urserOrOrgId varchar2(2000);
   urserOrOrgId:=idOrPath;
begin
  if idOrPath is null then
     return empty_clob();  
  end if;
  DBMS_LOB.CREATETEMPORARY(Result, TRUE); 
  while urserOrOrgId is not null loop
       ……  ……
        if tmpStr is null then
            DBMS_LOB.writeappend(Result,LENGTH(tmpId),tmpId);
            DBMS_LOB.writeappend(Result,LENGTH(';'),';');
        else
            DBMS_LOB.writeappend(Result,LENGTH(tmpStr),tmpStr);
            DBMS_LOB.writeappend(Result,LENGTH(';'),';');
        end if;  
  end loop;
  return(substr(Result,0,length(Result)-1));
end func_fa_getUNorOrgPath;

    但在java中读取数据时需要采用Stream进行读取