执行下面的procedure时提示我“ORA-01031:权限不足”,应该如何改写下面的procedure呢?
create or replace procedure pro_aaa (vew_name varchar2) as
dysql varchar2(800);
begin
dysql := 'create or replace view aaa_lijm
as select a.s_wage_id ,
SUM(CASE WHEN a.s_item_code = 1001 then a.n_item_value else 0 end) as item1001,
SUM(CASE WHEN a.s_item_code = 1002 then a.n_item_value else 0 end) as item1002,
SUM(CASE WHEN a.s_item_code = 1003 then a.n_item_value else 0 end) as item1003,
SUM(CASE WHEN a.s_item_code = 1004 then a.n_item_value else 0 end) as item1004,
SUM(CASE WHEN a.s_item_code = 1005 then a.n_item_value else 0 end) as item1005,
SUM(CASE WHEN a.s_item_code = 1006 then a.n_item_value else 0 end) as item1006
from WA_DATA a group by a.s_wage_id';
execute immediate dysql;
end;
create or replace procedure pro_aaa (vew_name varchar2) as
dysql varchar2(800);
begin
dysql := 'create or replace view aaa_lijm
as select a.s_wage_id ,
SUM(CASE WHEN a.s_item_code = 1001 then a.n_item_value else 0 end) as item1001,
SUM(CASE WHEN a.s_item_code = 1002 then a.n_item_value else 0 end) as item1002,
SUM(CASE WHEN a.s_item_code = 1003 then a.n_item_value else 0 end) as item1003,
SUM(CASE WHEN a.s_item_code = 1004 then a.n_item_value else 0 end) as item1004,
SUM(CASE WHEN a.s_item_code = 1005 then a.n_item_value else 0 end) as item1005,
SUM(CASE WHEN a.s_item_code = 1006 then a.n_item_value else 0 end) as item1006
from WA_DATA a group by a.s_wage_id';
execute immediate dysql;
end;
解决方案 »
- 安装ORACLE时报错,出现OUI-10038 错误,写高手解决
- 想从Access中通过SQL语句的方式导入数据到oracle中
- 游标的效率问题
- 关于connect by的问题
- 关于Oracle 10g 的操作
- 关于整表的^^请教!!!!
- 使用exp操作时有一些提示,请问是什么意思。
- 欢迎大家继续共享FTP,大量的Oracle学习书籍及应用手册、考试资料............
- 怎么样在过程中判断表是否存在,并DROP它......
- 怎么能写好sql语句
- 为什么grant create view to 'BIZDATA1';在下面的procedure中编译不过去?
- 帮我看下游标与存储过程实现这个功能.貌似游标下无法实现
create or replace procedure pro_aaa (vew_name varchar2) as
dysql varchar2(800);
begin
dysql := 'create or replace view aaa_lijm
as select a.s_wage_id ,
SUM(CASE WHEN a.s_item_code = 1001 then a.n_item_value else 0 end) as item1001,
SUM(CASE WHEN a.s_item_code = 1002 then a.n_item_value else 0 end) as item1002,
SUM(CASE WHEN a.s_item_code = 1003 then a.n_item_value else 0 end) as item1003,
SUM(CASE WHEN a.s_item_code = 1004 then a.n_item_value else 0 end) as item1004,
SUM(CASE WHEN a.s_item_code = 1005 then a.n_item_value else 0 end) as item1005,
SUM(CASE WHEN a.s_item_code = 1006 then a.n_item_value else 0 end) as item1006
from WA_DATA a group by a.s_wage_id';
grant execute on aaa_lijm to 'BIZDATA1'
execute immediate dysql;
end;
但是提示:
Error: PLS-00103: 出现符号 "GRANT"在需要下列之一时:
begindeclareendexception
exitforgotoifloopmodnullpragmaraisereturnselectupdatewhile
<an identifier><a double-quoted delimited-identifier>
<a bind variable><<closecurrentdeletefetchlockinsertopen
rollbacksavepointsetsqlexecutecommitforall
<a single-quoted SQL string>
Line: 13
Text: grant execute on aaa_lijm to 'BIZDATA1'Error: Hint: Parameter 'vew_name' is declared but never used in 'pro_aaa'
Line: 1
Text: create or replace procedure pro_aaa (vew_name varchar2) as
具体该怎样谢请给明示,本人对oracle初学。因此恳请说的详细些。
http://community.csdn.net/Expert/topic/5139/5139053.xml?temp=.3845484