create or replace procedure grant_prc is
cursor grant_audit is
select table_name,grantor,grantee,privilege from dba_tab_privs where owner=upper('pcisetl');
v_privilege varchar2(400);
v_grantee varchar2(400);
v_table_name varchar2(200);
v_grantor varchar2(200);
begin
open grant_audit;
loop
fetch grant_audit into v_table_name,v_grantor,v_grantee,v_privilege;
exit when grant_audit%NOTFOUND;
execute immediate 'grant '||v_privilege||' on '||v_grantor||'.'||v_table_name||' to '||v_grantee ||';';
end loop;
close grant_audit;
return;
end grant_prc;
/请问这个procedure有什么问题没有呢,我执行的时候就是不出结果,请帮助
cursor grant_audit is
select table_name,grantor,grantee,privilege from dba_tab_privs where owner=upper('pcisetl');
v_privilege varchar2(400);
v_grantee varchar2(400);
v_table_name varchar2(200);
v_grantor varchar2(200);
begin
open grant_audit;
loop
fetch grant_audit into v_table_name,v_grantor,v_grantee,v_privilege;
exit when grant_audit%NOTFOUND;
execute immediate 'grant '||v_privilege||' on '||v_grantor||'.'||v_table_name||' to '||v_grantee ||';';
end loop;
close grant_audit;
return;
end grant_prc;
/请问这个procedure有什么问题没有呢,我执行的时候就是不出结果,请帮助
解决方案 »
- plsql中to_char转换时间
- Oracle 日期范围问题
- Oracle 8i 下载地址?
- 关于 SELECT * FROM WHERE AA IN () 中的最大记录数的问题
- Oracle8.17 用户管理出现最新问题!!!!
- 在.NET中怎么创建oracle数据库
- 求教:用varchar(8)还是Number(8)作主键,效率如何?
- 重新安装oracle9i时,提示此sid在本计算机已经存在!
- 关于更新 如何对 两个表关联后查出的记录的某个表栏位进行更新???
- 在linux7.3上实现oracle8i的自动热备份
- Oracle数据导入IMP的性能问题(时间太长)
- 10W以上用户同时增加记录到服务器的同一个表上 如何保证操作能够顺利进行,不丢失数据,不等
试试
where owner='||upper('pcisetl')||';
begin
open grant_audit;
dbms_output.put_line(grant_audit%rowcount);
loop
fetch grant_audit into v_table_name,v_grantor,v_grantee,v_privilege;
exit when grant_audit%NOTFOUND;
dbms_output.put_line(grant '||v_privilege||' on '||v_grantor||'.'||v_table_name||' to '||v_grantee ||';');
execute immediate 'grant '||v_privilege||' on '||v_grantor||'.'||v_table_name||' to '||v_grantee ||';';
end loop;
close grant_audit;
return;
end grant_prc;
你在SQL Command窗口执行时,先set serveroutput on
再执行存储过程吧
SQL> exec grant_prc;PL/SQL procedure successfully completed
实际上我是想在procedure中批处理授权的。
可是执行还是没有输出哦。create or replace procedure grant_prc is
cursor grant_audit is
select table_name,grantor,grantee,privilege from dba_tab_privs where owner='upper(system)';
v_privilege varchar2(400);
v_grantee varchar2(400);
v_table_name varchar2(200);
v_grantor varchar2(200);
begin
open grant_audit;
loop
fetch grant_audit into v_table_name,v_grantor,v_grantee,v_privilege;
exit when grant_audit%NOTFOUND;
--dbms_output.put_line('succeed'grant_audit); execute immediate 'grant '||v_privilege||' on '||v_grantor||'.'||v_table_name||' to '||v_grantee ||';';
dbms_output.put_line('grant' ||','||v_privilege||' on '||v_table_name||' to '||v_grantee); end loop;
close grant_audit;
return;
end grant_prc;
看来主要是这句话有问题,可是我看不出啊,继续求助
exit when grant_audit%NOTFOUND;
就exit了,哪里有数据阿~~楼主写错了,应该这样用upper。
select table_name,grantor,grantee,privilege from dba_tab_privs where owner=upper('system');
不是这样
select table_name,grantor,grantee,privilege from dba_tab_privs where owner='upper(system)';
execute immediate 'grant '||v_privilege||' on '||v_grantor||'.'||v_table_name||' to '||v_grantee ||';';这一行有:
SQL> exec grant_prc;begin grant_prc; end;ORA-00911: invalid character
ORA-06512: at "SYSTEM.GRANT_PRC", line 16
ORA-06512: at line 1
grant EXECUTE on SYSTEM.ACTION_INPUT_DATA_NST to PUBLIC;
BEGIN grant_prc; END;*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "APPS.GRANT_PRC", line 14
ORA-06512: at line 1
SQL> grant EXECUTE on SYSTEM.ACTION_INPUT_DATA_NST to PUBLIC;
grant EXECUTE on SYSTEM.ACTION_INPUT_DATA_NST to PUBLIC
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> insufficient privileges 你要grant必须要有足够的权限,请问楼主有没有把权限grant 给 procedure??
SQL> exec grant_prc;begin grant_prc; end;ORA-00911: invalid character
ORA-06512: at "SYSTEM.GRANT_PRC", line 16
ORA-06512: at line 1
而且我就是用这个procedure的属主授权的哦
cursor grant_audit is
select table_name,grantor,grantee,privilege from dba_tab_privs where owner='upper(system)';
v_privilege varchar2(400);
v_grantee varchar2(400);
v_table_name varchar2(200);
v_grantor varchar2(200);
str long;
begin
open grant_audit;
loop
fetch grant_audit into v_table_name,v_grantor,v_grantee,v_privilege;
exit when grant_audit%NOTFOUND;
--dbms_output.put_line('succeed'grant_audit);
str:='grant '||v_privilege||' on '||v_grantor||'.'||v_table_name||' to '||v_grantee ||';';
dbms_output.put_line('str='||str);
end loop;
close grant_audit;
return;
end grant_prc;
把str输出出来手动执行下,看语句错哪了。
SQL> exec grant_prc;PL/SQL procedure successfully completed没有输出哦
SQL> exec grant_prc;
0PL/SQL procedure successfully completed可以不可能霉雨结果的,这个存储过程定位到用户system的。
create or replace procedure grant_prc is
cursor grant_audit is
select table_name,grantor,grantee,privilege from dba_tab_privs where owner='SYSTEM';
v_privilege varchar2(400);
v_grantee varchar2(400);
v_table_name varchar2(200);
v_grantor varchar2(200);
begin
open grant_audit;
dbms_output.put_line(grant_audit%rowcount); --查看复合条件的记录数
loop
fetch grant_audit into v_table_name,v_grantor,v_grantee,v_privilege;
exit when grant_audit%NOTFOUND;
--dbms_output.put_line('succeed'grant_audit); --execute immediate 'grant '||v_privilege||' on '||v_grantor||'.'||v_table_name||' to '||v_grantee ||';';
dbms_output.put_line('grant ' ||v_privilege||' on '||v_table_name||' to '||v_grantee||';'); end loop;
close grant_audit;
return;
end grant_prc;