由于要用游标返回结果集,所以存储过程建立在oracle的程序包中。程序包不允许用authid current_user授权,网上说可以直接用grant create any table to system,但是我试了很多次就是不成功。希望哪位高手帮我解答一下。我的代码如下:
create or replace
package body myPackage2
is
procedure GET_TOTALDEMANDINFO2 (ret_cursor out mycursor)
as
str varchar2(500);
dptable varchar2(100):='drop table retTab';
begin
--GRANT CREATE ANY TABLE TO SYSTEM;
--CONNECT SYSTEM/123@zsygis;
EXECUTE IMMEDIATE DPTABLE;
str:='create global temporary table retTab(var_city varchar2(20),DEMAND_TOTAL number(20,5),DEMAND_TOTALGROWTH number(10,6)) ON COMMIT preserve ROWS';
execute immediate str;
insert into retTab
select CITY,DEMAND_TOTAL,DEMAND_TOTALGROWTH
from C_CITY_ECO;
--open ret_cursor for select * from retTab;
end GET_TOTALDEMANDINFO2;
end myPackage2;
oracle存储过程临时表授权
create or replace
package body myPackage2
is
procedure GET_TOTALDEMANDINFO2 (ret_cursor out mycursor)
as
str varchar2(500);
dptable varchar2(100):='drop table retTab';
begin
--GRANT CREATE ANY TABLE TO SYSTEM;
--CONNECT SYSTEM/123@zsygis;
EXECUTE IMMEDIATE DPTABLE;
str:='create global temporary table retTab(var_city varchar2(20),DEMAND_TOTAL number(20,5),DEMAND_TOTALGROWTH number(10,6)) ON COMMIT preserve ROWS';
execute immediate str;
insert into retTab
select CITY,DEMAND_TOTAL,DEMAND_TOTALGROWTH
from C_CITY_ECO;
--open ret_cursor for select * from retTab;
end GET_TOTALDEMANDINFO2;
end myPackage2;
oracle存储过程临时表授权
grant create any table to system 是将创建表的权限赋予给system用户
你的这段存储过程是在那个用户下?你就要to 那个用户