CREATE OR REPLACE PROCEDURE TEST2
AS
cursor cl is select * from jkzb where xybz='g' order by zbdm;
rec cl%rowtype;
BEGIN
open cl;
loop
fetch cl into rec;
p_test(rec.zbdm,rec.zbmc,rec.zbms,rec.ywml_dm,
rec.jklx_dm,rec.xglx_dm,rec.xgff,rec.zrbm_dm,
rec.sclb_sql,rec.lyb_sqlxg,rec.gltj_sql,
rec.fztj_sql,rec.pxtj_sql);
exit when cl%notfound;
end loop;
close cl;
END;
create or replace procedure p_test(v_zbdm in varchar,v_zbmc in varchar,
v_zbms in varchar,v_ywml_dm in varchar,v_jklx_dm in varchar,
v_xglx_dm in varchar,v_xgff in varchar,v_zrbm_dm in varchar,
v_sc in varchar,v_ly in varchar,
v_tj in varchar,v_fz in varchar,v_px in varchar)
is
type cur_type is ref cursor;
cur cur_type;
str varchar2(32767);
v_nsrsbh varchar2(20);
v_nsr_swjg_dm varchar2(11);
v_lrr_dm varchar2(11);
v_lrrq date;
v_xgr_dm varchar2(11);
v_xgrq date;
v_gjzd varchar2(100);
begin
str:='';
str:='select '||ltrim(rtrim(v_sc))||' from '||ltrim(rtrim(v_ly))||' where '||ltrim(rtrim(v_tj))||'';
/*if v_fz is not null
then
str:=str+''||v_fz||'';
end if;
if v_px is not null
then
str:=str+''||v_px||'';
end if;*/
if cur%isopen then
close cur;
else
open cur for str;
end if;
loop
fetch cur into
v_nsrsbh,v_nsr_swjg_dm,v_lrr_dm,v_lrrq,v_xgr_dm,v_xgrq,v_gjzd;
exit when cur%notfound;
null;
end loop;
close cur;
commit;
end; test2调用P_TEST
问题如下:
过程test2游标c1循环体调用过程P_TEST,P_TEST中定义了动态游标cur,c1第一次循环调用正常,c1第二次循环调用报错SQL> exec test2
begin test2; end;
ORA-01031: insufficient privileges
ORA-06512: at "ZGSJJK.P_TEST", line 38
ORA-06512: at "ZGSJJK.TEST2", line 9
ORA-06512: at line 2
问题描述:
过程test2游标c1循环体调用过程P_TEST,P_TEST中定义了动态游标cur,过程test2游标c1循环,第一次调用过程P_TEST正常,P_TEST中已关闭游标,第二次调用报错,定位到过程P_TEST中open cur for str;
不知道怎么原因,请高手帮忙解决以下
AS
cursor cl is select * from jkzb where xybz='g' order by zbdm;
rec cl%rowtype;
BEGIN
open cl;
loop
fetch cl into rec;
p_test(rec.zbdm,rec.zbmc,rec.zbms,rec.ywml_dm,
rec.jklx_dm,rec.xglx_dm,rec.xgff,rec.zrbm_dm,
rec.sclb_sql,rec.lyb_sqlxg,rec.gltj_sql,
rec.fztj_sql,rec.pxtj_sql);
exit when cl%notfound;
end loop;
close cl;
END;
create or replace procedure p_test(v_zbdm in varchar,v_zbmc in varchar,
v_zbms in varchar,v_ywml_dm in varchar,v_jklx_dm in varchar,
v_xglx_dm in varchar,v_xgff in varchar,v_zrbm_dm in varchar,
v_sc in varchar,v_ly in varchar,
v_tj in varchar,v_fz in varchar,v_px in varchar)
is
type cur_type is ref cursor;
cur cur_type;
str varchar2(32767);
v_nsrsbh varchar2(20);
v_nsr_swjg_dm varchar2(11);
v_lrr_dm varchar2(11);
v_lrrq date;
v_xgr_dm varchar2(11);
v_xgrq date;
v_gjzd varchar2(100);
begin
str:='';
str:='select '||ltrim(rtrim(v_sc))||' from '||ltrim(rtrim(v_ly))||' where '||ltrim(rtrim(v_tj))||'';
/*if v_fz is not null
then
str:=str+''||v_fz||'';
end if;
if v_px is not null
then
str:=str+''||v_px||'';
end if;*/
if cur%isopen then
close cur;
else
open cur for str;
end if;
loop
fetch cur into
v_nsrsbh,v_nsr_swjg_dm,v_lrr_dm,v_lrrq,v_xgr_dm,v_xgrq,v_gjzd;
exit when cur%notfound;
null;
end loop;
close cur;
commit;
end; test2调用P_TEST
问题如下:
过程test2游标c1循环体调用过程P_TEST,P_TEST中定义了动态游标cur,c1第一次循环调用正常,c1第二次循环调用报错SQL> exec test2
begin test2; end;
ORA-01031: insufficient privileges
ORA-06512: at "ZGSJJK.P_TEST", line 38
ORA-06512: at "ZGSJJK.TEST2", line 9
ORA-06512: at line 2
问题描述:
过程test2游标c1循环体调用过程P_TEST,P_TEST中定义了动态游标cur,过程test2游标c1循环,第一次调用过程P_TEST正常,P_TEST中已关闭游标,第二次调用报错,定位到过程P_TEST中open cur for str;
不知道怎么原因,请高手帮忙解决以下
没有权限
close cur;
else
open cur for str;
end if; 这句为什么要这样写?直接:open cur for str;不就OK了么?
select lyb_sqlxg from jkzb where xybz='g'
确认一下当前用户对查询出来的表名所对应的表是否具有权限
否则可能要授权
grant select on <表名> to <建立存储过程的用户>