关于Oracle的表函数问题

Question

照着 ：
http://www.cnblogs.com/Godblessyou/archive/2010/07/17/1779633.html
http://www.cnblogs.com/liguiqing/archive/2009/05/26/1489243.html
这2个地址写了个表函数，使用pipelined 方式的和使用普通方式的，写好后，都是执行成功，编印出错！求助哪里出了问题。。。。

 create or replace type SealData As object
(
   BE_ID  Char(36),

   JYZ_PIC nvarchar2(2000),

   GCJZ_PIC nvarchar2(2000),

   CMA_PIC nvarchar2(2000),

   CAL_PIC nvarchar2(2000),

   CNAS_PIC nvarchar2(2000)
);

create or replace type Seal_table As table of SealData;

 create or replace function GetSeal(beid char(36))
  return Seal_table Pipelined
As 
   res SealData;
Begin
   For rw In ( 
          Select beid As BE_ID,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='检验专用章' And c.Id= beid
          ) As JYZ_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='工程见证' And c.Id= beid
          ) As GCJZ_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='CMA' And c.Id= beid
          ) As CMA_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='CAL' And c.Id= beid
          ) As CAL_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='CNAS' And c.Id= beid
          ) As CNAS_PIC
          From dual     
        )     
       Loop 
         res := SealData(rw.BE_ID,rw.JYZ_PIC,rw.GCJZ_PIC,rw.CMA_PIC,rw.CAL_PIC,rw.CNAS_PIC); 
         Pipe Row(res);
      End Loop;
      Return;     
End ;

 create or replace function GetSeal(beid char(36))
  return Seal_table 
As 
   res Seal_table := Seal_table();
Begin
   For rw In ( 
          Select beid As BE_ID,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='检验专用章' And c.Id= beid
          ) As JYZ_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='工程见证' And c.Id= beid
          ) As GCJZ_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='CMA' And c.Id= beid
          ) As CMA_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='CAL' And c.Id= beid
          ) As CAL_PIC,
          (select b.Imageaddress from limsbusinessenregisterinfo c
          left join LIMSBEElectronicSealInfo a on c.id=a.enregisterid
          inner join LIMSCheckSealInfo b on a.electronicsealinfoid=b.electronicsealid 
          and c.checkqualificationid=b.checkqualificationid
          left join LIMSElectronicSealInfo d on b.electronicsealid=d.id
          Where d.Electronicname='CNAS' And c.Id= beid
          ) As CNAS_PIC
          From dual     
        )     
       Loop 
       res.extend;
       res(res.count):=SealData(rw.BE_ID,rw.JYZ_PIC,rw.GCJZ_PIC,rw.CMA_PIC,rw.CAL_PIC,rw.CNAS_PIC);        
      End Loop;
      Return res;     
End GetSeal;

看2个函数，不知道是不是因为 for in 后面跟的是 dual 方式的表？不清楚是不是这问题。。。。

Answer 1

 create or replace function GetSeal(beid char(36))   

函数的参数错误：
1.不能传 char(36) ，若使用char36 函数只能生成成功，不能编译通过。

2.不能只单独传 char ，若只传传char，虽然能生成成功，能编印通过，但是在使用的时候会报 “ ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小”。

正确写法：

根据 接收返回类型 必须传入可变类型，in varchar2 或者 in nvarchar2 。