各位大侠帮帮忙,我想通过如下存储过程返回结果集,编译没问题,
执行:输入v_co_nbr的值,点执行,然后点游标v_outmsg 后面的省略号按钮,报错:ORA-24338:未执行语句句柄
不知道该怎么处理?请各位帮忙哦!
存储过程如下:
create or replace procedure P_SEARCH_ERROR(v_CO_NBR in varchar2,
v_outmsg out SYS_REFCURSOR)
AS
v_cfs_co_nbr varchar2(20);
v_cfs_co_id varchar2(16);
oe_hand_flag char(1);
oe_err_info varchar2(2000);
oe_order_error_id number;
begin
open v_outmsg for select distinct cfs.co_nbr,
cfs.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
from sp.crm_for_sa cfs, sp.order_error oe
where cfs.co_id = oe.co_id
and cfs.co_id in (select co_id
from crm.cust_order co
where co.co_nbr = v_CO_NBR)
and oe.hand_flag = 'D'
union all
select distinct cfs.co_nbr,
cfs.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
from sp.crm_for_sa cfs, sp.order_error oe
where cfs.order_id = oe.order_id
and cfs.co_nbr = v_CO_NBR
and oe.hand_flag = 'D'
union all
select distinct co.co_nbr,
co.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
from crm.cust_order co, sp.order_error oe
where co.co_id = oe.co_id
and co.co_nbr =v_CO_NBR
and oe.hand_flag = 'D';loop
fetch v_outmsg into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id;
exit when v_outmsg%notfound;
dbms_output.put_line('v_cfs_co_nbr:'||v_cfs_co_nbr) ;
dbms_output.put_line('v_cfs_co_id:'||v_cfs_co_id) ;
dbms_output.put_line('oe_hand_flag:'||oe_hand_flag) ;
dbms_output.put_line('oe_err_info:'||oe_err_info) ;
dbms_output.put_line('oe_order_error_id:'||oe_order_error_id) ;
end loop;
close v_outmsg;
exception
WHEN OTHERS THEN
rollback;
end;
执行:输入v_co_nbr的值,点执行,然后点游标v_outmsg 后面的省略号按钮,报错:ORA-24338:未执行语句句柄
不知道该怎么处理?请各位帮忙哦!
存储过程如下:
create or replace procedure P_SEARCH_ERROR(v_CO_NBR in varchar2,
v_outmsg out SYS_REFCURSOR)
AS
v_cfs_co_nbr varchar2(20);
v_cfs_co_id varchar2(16);
oe_hand_flag char(1);
oe_err_info varchar2(2000);
oe_order_error_id number;
begin
open v_outmsg for select distinct cfs.co_nbr,
cfs.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
from sp.crm_for_sa cfs, sp.order_error oe
where cfs.co_id = oe.co_id
and cfs.co_id in (select co_id
from crm.cust_order co
where co.co_nbr = v_CO_NBR)
and oe.hand_flag = 'D'
union all
select distinct cfs.co_nbr,
cfs.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
from sp.crm_for_sa cfs, sp.order_error oe
where cfs.order_id = oe.order_id
and cfs.co_nbr = v_CO_NBR
and oe.hand_flag = 'D'
union all
select distinct co.co_nbr,
co.co_id,
oe.hand_flag,
oe.err_info,
oe.order_error_id
from crm.cust_order co, sp.order_error oe
where co.co_id = oe.co_id
and co.co_nbr =v_CO_NBR
and oe.hand_flag = 'D';loop
fetch v_outmsg into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id;
exit when v_outmsg%notfound;
dbms_output.put_line('v_cfs_co_nbr:'||v_cfs_co_nbr) ;
dbms_output.put_line('v_cfs_co_id:'||v_cfs_co_id) ;
dbms_output.put_line('oe_hand_flag:'||oe_hand_flag) ;
dbms_output.put_line('oe_err_info:'||oe_err_info) ;
dbms_output.put_line('oe_order_error_id:'||oe_order_error_id) ;
end loop;
close v_outmsg;
exception
WHEN OTHERS THEN
rollback;
end;
把过程里面的close v_outmsg;去掉--确定打开游标后面的sql 语句没问题
fetch v_outmsg into v_cfs_co_nbr,v_cfs_co_id,oe_hand_flag,oe_err_info,oe_order_error_id;
exit when v_outmsg%notfound;
dbms_output.put_line('v_cfs_co_nbr:'||v_cfs_co_nbr) ;
dbms_output.put_line('v_cfs_co_id:'||v_cfs_co_id) ;
dbms_output.put_line('oe_hand_flag:'||oe_hand_flag) ;
dbms_output.put_line('oe_err_info:'||oe_err_info) ;
dbms_output.put_line('oe_order_error_id:'||oe_order_error_id) ;
end loop;
close v_outmsg;
替换成以下SQL即可!loop
dbms_output.put_line('v_cfs_co_nbr:'||v_outmsg.co_nbr) ;
dbms_output.put_line('v_cfs_co_id:'||v_outmsg.co_id) ;
dbms_output.put_line('oe_hand_flag:'||v_outmsg.hand_flag) ;
dbms_output.put_line('oe_err_info:'||v_outmsg.err_info) ;
dbms_output.put_line('oe_order_error_id:'||v_outmsg.order_error_id) ;
end loop;