全部博文(1144)
分类: Oracle
2012-06-20 21:30:55
遇到一个问题,如下: ETL@RACTEST> begin 2 DBMS_LOCK.sleep(1); 3 end; 4 / PL/SQL procedure successfully completed. 但是 ETL@RACTEST> CREATE OR REPLACE PROCEDURE etl.pliutest 2 authid current_user 3 AS 4 BEGIN 5 DBMS_LOCK.sleep(1); 6 END; 7 / Warning: Procedure created with compilation errors. Elapsed: 00:00:00.07 ETL@RACTEST> ETL@RACTEST> show errors Errors for PROCEDURE ETL.PLIUTEST: LINE/COL ERROR -------- ----------------------------------------------------------------- 5/2 PL/SQL: Statement ignored 5/2 PLS-00201: identifier 'DBMS_LOCK' must be declared 同一个包,在相同的用户下的匿名程序块中可以执行,但是在存储过程中却没有权限执行,查了下资料,一些DBMS_开头的pkg在pl/sql中用到的时候,需要显示的执行 grant execute on dbms_xxxx to xxx 来赋下权限才可以。此外这个授权system用户是没有权限的,要sys 或者plsql / as dba登录才有权限授权。 授权以后可以看到: SYSTEM@RACTEST> select grantee,owner,table_name,grantor,privilege from dba_tab_privs where table_name like 'DBMS_LOCK'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE -------------------- ---------- ---------- ---------- ---------- EXECUTE_CATALOG_ROLE SYS DBMS_LOCK SYS EXECUTE DMSYS SYS DBMS_LOCK SYS EXECUTE CTXSYS SYS DBMS_LOCK SYS EXECUTE EXFSYS SYS DBMS_LOCK SYS EXECUTE MDSYS SYS DBMS_LOCK SYS EXECUTE OLAPSYS SYS DBMS_LOCK SYS EXECUTE SYSMAN SYS DBMS_LOCK SYS EXECUTE ETL SYS DBMS_LOCK SYS EXECUTE 8 rows selected. 然后再次执行: SYSTEM@RACTEST> CREATE OR REPLACE PROCEDURE etl.pliutest 2 authid current_user 3 AS 4 BEGIN 5 DBMS_LOCK.sleep(1); 6 END; 7 / Procedure created. OK了! |