新博客http://www.cnblogs.com/zhjh256 欢迎访问
分类: Oracle
2008-04-16 20:16:39
自定义type类型过小引发的错误。
整个过程如下:
declare
i_prm_aul_list varchar2(50);
o_issue_info_cursor sys_refcursor;
issue_id NUMBER;
prm_pm_nm VARCHAR2(50);
prm_rcaower_nm VARCHAR2(50);
begin
i_prm_aul_list := '512,546';
timt_get_pm_rcaowner(i_prm_aul_list => i_prm_aul_list,
o_issue_info_cursor => o_issue_info_cursor);
loop
exit when o_issue_info_cursor%notfound;
fetch o_issue_info_cursor
into issue_id, prm_pm_nm, prm_rcaower_nm;
dbms_output.put_line('issue_id = ' || issue_id);
dbms_output.put_line('prm_pm_nm = ' || prm_pm_nm);
dbms_output.put_line('prm_rcaower_nm = ' || prm_rcaower_nm);
end loop;
end;
ORA-01001: invalid cursor
ORA-06512: at line 13
检查了相关文档以及metalink后,将游标循环移到proc内部输出,如下:
SQL> declare
2 i_prm_aul_list varchar2(50);
3 o_issue_info_cursor sys_refcursor;
4
5 begin
6 i_prm_aul_list := '512,546';
7 timt_get_pm_rcaowner(i_prm_aul_list,
8 o_issue_info_cursor);
9 end;
10 /
v_Count = 2
v_issue_List = ,512,546,
v_issue_info_list.last = 1
this error message is from procedure timt_get_pm_rcaowner;
SQLCODE: -6502
SQLERRM: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
PL/SQL procedure successfully completed
错误消失了,出现了ORA-06502。并且proc里面没有insert,select..into,跟踪后发现在一个将数据送入一个嵌套表时跳入异常,因此增加了嵌套表存储的元素的大小。
重新执行后,没有报错;然后将游标循环移到外面,也没有报错,错误解决。