Oracle PL/SQL之不能在function里面调用DBMS_LOCK（Grant to role OR Grant to user）

已知：测试用户tuser1，测试角色trole1，trole1已经授权给了tuser1。

在测试一段程序时需要用到延时，于是就把dbms_lock授权给了trole1，放在匿名块里测试没有问题：

SQL> set serveroutput on; SQL> SQL> BEGIN 2 dbms_output.put_line(systimestamp); 3 -- dbms_backup_restore.sleep(3); 4 dbms_lock.sleep(3); 5 dbms_output.put_line(systimestamp); 6 END; 7 / 24-JUN-11 04.30.57.722000000 PM +08:00 24-JUN-11 04.31.00.723000000 PM +08:00 PL/SQL procedure successfully completed SQL>

 

但是把它移到function里面却报错了：

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as tuser1 SQL> SQL> CREATE OR REPLACE FUNCTION sum2(p_deptno IN NUMBER) RETURN NUMBER IS 2 l_ret NUMBER; 3 BEGIN 4 dbms_lock.sleep(5); 5 SELECT SUM(sal) 6 INTO l_ret 7 FROM emp 8 WHERE deptno = p_deptno; 9 RETURN l_ret; 10 END sum2; 11 / Warning: Function created with compilation errors SQL> show err; Errors for FUNCTION TUSER1.SUM2: LINE/COL ERROR -------- -------------------------------------------------- 4/3 PLS-00201: identifier 'DBMS_LOCK' must be declared 4/3 PL/SQL: Statement ignored

 

很奇怪，难道不能在function里面用dbms_lock，google一下，找到答案：

必须把dbms_lock的execute权限直接grant给用户才行，只grant给角色就会报错。

REF：http://forums.oracle.com/forums/thread.jspa?threadID=902392&tstart=0&messageID=3481832

 

现在function可以正常创建了，不过为什么呢？怎么会存在grant to user和grant to role在某些object上有区别？

SQL> SQL> CREATE OR REPLACE FUNCTION sum2(p_deptno IN NUMBER) 2 RETURN NUMBER IS PRAGMA AUTONOMOUS_TRANSACTION; 3 l_ret NUMBER; 4 BEGIN 5 dbms_lock.sleep(5); 6 --DBMS_BACKUP_RESTORE.SLEEP(5); 7 dbms_output.put_line(systimestamp); 8 SELECT SUM(sal) 9 INTO l_ret 10 FROM emp 11 WHERE deptno = p_deptno; 12 RETURN l_ret; 13 END sum2; 14 / Function created SQL>