declare
type flag_cur_type is ref cursor;
flag_cur flag_cur_type;
temp_item varchar2(16);
temp_num number;
type table_type is table of error_item_obj;
temp_tab table_type;
begin
open flag_cur ;
select error_item_obj(flag, num) from
(select d.flag, count(1) as num from
(select substr(c.result_msg, 1, instr(c.result_msg, ' ')) as flag from
(select a.msg_id,b.result_msg from jxjk.inf_bill_order a, jxjk.inf_bill_order_result b
where a.write_date>to_date('2011-2-19 08:00:00','yyyy-mm-dd hh24:mi:ss')
and a.sys_flag like '5%' and a.main_id = b.main_id and b.sys_flag like '5%') c) d
group by d.flag);
fetch flag_cur bulk collect into temp_tab;
close flag_cur;
/* select a.item, a.num into temp_item, temp_num from temp_tab a
where num = (select max(b.num) from temp_tab b) ;
dbms_output.put_line(temp_item || ' ' || temp_num);*/
end; 注:
create type error_item_obj as object
(
item varchar(16),
num number(4)
); 报错
ora-22814:attribute or element value is larger than specified in type麻烦各位大哥大姐,看看。
type flag_cur_type is ref cursor;
flag_cur flag_cur_type;
temp_item varchar2(16);
temp_num number;
type table_type is table of error_item_obj;
temp_tab table_type;
begin
open flag_cur ;
select error_item_obj(flag, num) from
(select d.flag, count(1) as num from
(select substr(c.result_msg, 1, instr(c.result_msg, ' ')) as flag from
(select a.msg_id,b.result_msg from jxjk.inf_bill_order a, jxjk.inf_bill_order_result b
where a.write_date>to_date('2011-2-19 08:00:00','yyyy-mm-dd hh24:mi:ss')
and a.sys_flag like '5%' and a.main_id = b.main_id and b.sys_flag like '5%') c) d
group by d.flag);
fetch flag_cur bulk collect into temp_tab;
close flag_cur;
/* select a.item, a.num into temp_item, temp_num from temp_tab a
where num = (select max(b.num) from temp_tab b) ;
dbms_output.put_line(temp_item || ' ' || temp_num);*/
end; 注:
create type error_item_obj as object
(
item varchar(16),
num number(4)
); 报错
ora-22814:attribute or element value is larger than specified in type麻烦各位大哥大姐,看看。
declare
type flag_cur_type is ref cursor;
flag_cur flag_cur_type;
temp_item varchar2(16);
temp_num number;
type table_type is table of error_item_obj;
temp_tab table_type;
begin
open flag_cur for select error_item_obj(flag, num)
from (select d.flag, count(1) as num
from (select substr(c.result_msg, 1, instr(c.result_msg, ' ')) as flag
from (select a.msg_id, b.result_msg
from jxjk.inf_bill_order a,
jxjk.inf_bill_order_result b
where a.write_date >
to_date('2011-2-19 08:00:00',
'yyyy-mm-dd hh24:mi:ss')
and a.sys_flag like '5%'
and a.main_id = b.main_id
and b.sys_flag like '5%') c) d
group by d.flag); fetch flag_cur bulk collect
into temp_tab;
close flag_cur; /* select a.item, a.num into temp_item, temp_num from temp_tab a
where num = (select max(b.num) from temp_tab b) ;
dbms_output.put_line(temp_item || ' ' || temp_num);*/end;
--ORA-22814:attribute or element value is larger than specified in type
--Cause: Value provided for the object type attribute or
--collection element exceeded the size specified in the type declaration.
--Action:Choose another value and retry the operation.
下面那一行注释掉的语句。报错了。我想让嵌套表像普通表一样使用。取表里最大的num来取信息。
发现报错了。
select a.item, a.num into temp_item, temp_num from temp_tab a
where num = (select max(b.num) from temp_tab b) ;