SQL> select count(*) from dba_users;
COUNT(*)
----------
31没有问题下面:
create or replace procedure ckuName(userName varchar2)
as
num integer;
userName_local varchar2(20);
begin
userName_local := upper(userName);
select count(*) into num from DBA_USERS ;
if num>0 then
execute immediate 'drop user '||userName;
dbms_output.put_line('用户删除成功');
else
dbms_output.put_line('用户不存在');
end if;
end;
/
出错:
Warning: Procedure created with compilation errors
SQL> show error;
Errors for PROCEDURE SYSTEM.CKUNAME:
LINE/COL ERROR
-------- ---------------------------------
7/38 PL/SQL: ORA-00942: 表或视图不存在7/8 PL/SQL: SQL Statement ignored
COUNT(*)
----------
31没有问题下面:
create or replace procedure ckuName(userName varchar2)
as
num integer;
userName_local varchar2(20);
begin
userName_local := upper(userName);
select count(*) into num from DBA_USERS ;
if num>0 then
execute immediate 'drop user '||userName;
dbms_output.put_line('用户删除成功');
else
dbms_output.put_line('用户不存在');
end if;
end;
/
出错:
Warning: Procedure created with compilation errors
SQL> show error;
Errors for PROCEDURE SYSTEM.CKUNAME:
LINE/COL ERROR
-------- ---------------------------------
7/38 PL/SQL: ORA-00942: 表或视图不存在7/8 PL/SQL: SQL Statement ignored
create or replace procedure system.ckuName(userName varchar2)
as
num integer;
userName_local varchar2(20);
begin
userName_local := upper(userName);
select count(*) into num from DBA_USERS ;
if num >0 then
execute immediate 'drop user ' ||userName;
dbms_output.put_line( '用户删除成功 ');
else
dbms_output.put_line( '用户不存在 ');
end if;
end;
这样在pl/sql developer执行也会报错的,问题是出在system.上,但是原因不知道!编译成功的代码如下
create or replace procedure ckuName(userName varchar2)
as
num integer;
userName_local varchar2(20);
begin
userName_local := upper(userName);
select count(*) into num from DBA_USERS ;
if num >0 then
execute immediate 'drop user ' ||userName;
dbms_output.put_line( '用户删除成功 ');
else
dbms_output.put_line( '用户不存在 ');
end if;
end;
还是有问题,不过我把相同的代码在 sys 模式下运行
却是对的
确实是没有权限,当我在 sys 下运行一条语句 grant select on dba_users to system; 然后,再运行上面的过程
没有出错,通过了. 难理解的是,为什么单独执行 select * from dba_users; 是有权限的,
而在过程里面执行 select count(*) into num from dba_users; 却没有权限了.
还有一个疑问:
就是上述过程成功创建后执行调用语句:execute ckuName('admin');是出错,提示是:权限不足.与上面相同,单独执行:drop users admin ; 删除成功.
用过程执行:失败,权限不足.然后再在 sys 模式下授权 : grant drop user to system; 调用:execute ckuName('admin'); 运行成功.总结出疑问:为什么 system 用户下,单独执行一条查询语句: select * from dba_users; 成功得到查询结果
而该将查询语句 写入过程 就权限不足了? 为什么 system 用户下,单独执行语句:drop user admin; 可以运行成功,
而将该项语句放入过程 又 是权限不足了?