Wayne
![Avatar](/user_files/wayne/config/avatar.png?1375032448)
![Table_bottom](/images/table_bottom.jpg?1375031774)
Calendar
August | ||||||
---|---|---|---|---|---|---|
Sun | Mon | Tue | Wed | Thu | Fri | Sat |
28 | 29 | 30 | 31 | 1 | 2 | 3 |
4 | 5 | 6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 | 16 | 17 |
18 | 19 | 20 | 21 | 22 | 23 | 24 |
25 | 26 | 27 | 28 | 29 | 30 | 31 |
![Table_bottom](/images/table_bottom.jpg?1375031774)
Categories
![Table_bottom](/images/table_bottom.jpg?1375031774)
Search
![Table_bottom](/images/table_bottom.jpg?1375031774)
Random Posts
![Table_bottom](/images/table_bottom.jpg?1375031774)
Counter
387715
![Table_bottom](/images/table_bottom.jpg?1375031774)
Hot Posts
![Table_bottom](/images/table_bottom.jpg?1375031774)
New Comments
![Table_bottom](/images/table_bottom.jpg?1375031774)
New Messages
![Table_bottom](/images/table_bottom.jpg?1375031774)
Links
![Table_bottom](/images/table_bottom.jpg?1375031774)
RSS
![Table_bottom](/images/table_bottom.jpg?1375031774)
PLS-00801: Type xxxxxx has no MAP method 这到底是啥啊……
Wayne
posted @ Thu, 28 Feb 2013 13:48:25 +0000
in Experience
, 3315 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')) )
结果也是好好的,完全符合预期。真是无厘头啊,这都什么情况啊……
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.