PLS-00801: Type xxxxxx has no MAP method 这到底是啥啊……

Wayne posted @ Thu, 28 Feb 2013 13:48:25 +0000 in Experience , 3160 readers

前段时间把一个查询效率很低的视图给改成了 带一个日期参数的函数,输入一个日期,返回相应的结果集。但是这样没能完整地复制视图原有的功能,比如给定一个日期区间的查询就做不到了。于是今天给扩充了一下,然后就遇到了这个该死的莫名其妙的PLS-00801.

代码呢是这样的:

create table stockstructure (
     oc_date         NUMBER(10) ,
     exchange_type   CHAR(4) ,
     stock_code      CHAR(12) ,
     total_amount    NUMBER(20,3),
     turnover_amount NUMBER(20,3) ,
     remark          VARCHAR2(2000 )
  );

create or replace type rec_stockstructure is OBJECT(
     oc_date         NUMBER(10 ) ,
     exchange_type   CHAR(4 ) ,
     stock_code      CHAR(12 ) ,
     total_amount    NUMBER(20 ,3),
     turnover_amount NUMBER(20 ,3) ,
     remark          VARCHAR2(2000 )
  );

create or replace type ty_stockstructure is table of rec_stockstructure;

create or replace function fn_get_stockstructure(
     p_oc_date hstype.HsDate,
     p_end_date hstype.hsdate default 0
  )
return ty_stockstructure
is
    tmp_stockstructure ty_stockstructure := ty_stockstructure();
    rs_stockstructure ty_stockstructure := ty_stockstructure();
begin
    if p_end_date = 0 then
       select rec_stockstructure(p_oc_date,a.exchange_type,a.stock_code,a.total_amount,a.turnover_amount,a.remark)
          bulk collect into rs_stockstructure
         from stockstructure a
       where (a.oc_date,a.exchange_type,a.stock_code) in
                   ( select max (t.oc_date),t.exchange_type,t.stock_code from stockstructure t
                    where oc_date<=p_oc_date
                      group by t.exchange_type,t.stock_code
                   )
              ;
    else
         for xdate in 0..(to_date(p_end_date,'yyyymmdd' )-to_date(p_oc_date,'yyyymmdd')) loop
                select rec_stockstructure(to_char(to_date(p_oc_date,'yyyymmdd' )+xdate,'yyyymmdd'),a.exchange_type,a.stock_code,a.total_amount,a.turnover_amount,a.remark)
                  bulk collect into tmp_stockstructure
                 from stockstructure a
               where (a.oc_date,a.exchange_type,a.stock_code) in
                           ( select max (t.oc_date),t.exchange_type,t.stock_code from stockstructure t
                             where oc_date<=to_char(to_date(p_oc_date,'yyyymmdd' )+xdate,'yyyymmdd')
                            group by t.exchange_type,t.stock_code
                           )
                ;
               rs_stockstructure := rs_stockstructure multiset union tmp_stockstructure;    --------------------- 报错位置
          end loop ;     
    end if;        
   return rs_stockstructure;
end;

然后就在我标着的位置给抛出了一个错误。错误原文如此:

Error: PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2079; Type 0x0x2b89f96a66e0 has no MAP method.; 

于是我就纠结了,这到底说的是啥呢?这几个关键字我连搜索都没法搜。而且更纠结的是,在Oracle 11g 上不报错,在 Oracle 10g 上报错。那么是版本问题嘛?我查了下,10g multiset 的支持完全没问题啊。我又在报错的这个数据库上做了个这样的测试:

select * from table( 
   ty_stockstructure(rec_stockstructure(1234,'1','1234',1,1,'1')) 
  multiset union 
   ty_stockstructure(rec_stockstructure(2234,'2','2234',2,2,'2')) 
)

结果也是好好的,完全符合预期。真是无厘头啊,这都什么情况啊……

essay writing compan said:
Tue, 18 Sep 2018 16:09:27 +0000

This login information is incomplete.Let me take some guidance from active forums.May be I get some satisfactory solution of this problem.Thanks.


Login *


loading captcha image...
(type the code from the image)
or Ctrl+Enter