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

已知：測試使用者tuser1，測試角色trole1，trole1已經授權給了tuser1。

在測試一段程式時需要用到延時，於是就把dbms_lock授權給了trole1，放在匿名塊裡測試沒有問題：

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

 

但是把它移到function裡面卻報錯了：

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0<br />Connected as tuser1</p><p>SQL><br />SQL> CREATE OR REPLACE FUNCTION sum2(p_deptno IN NUMBER) RETURN NUMBER IS<br /> 2 l_ret NUMBER;<br /> 3 BEGIN<br /> 4 dbms_lock.sleep(5);<br /> 5 SELECT SUM(sal)<br /> 6 INTO l_ret<br /> 7 FROM emp<br /> 8 WHERE deptno = p_deptno;<br /> 9 RETURN l_ret;<br /> 10 END sum2;<br /> 11 /</p><p>Warning: Function created with compilation errors</p><p>SQL> show err;<br />Errors for FUNCTION TUSER1.SUM2:</p><p>LINE/COL ERROR<br />-------- --------------------------------------------------<br />4/3 PLS-00201: identifier 'DBMS_LOCK' must be declared<br />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><br />SQL> CREATE OR REPLACE FUNCTION sum2(p_deptno IN NUMBER)<br /> 2 RETURN NUMBER IS PRAGMA AUTONOMOUS_TRANSACTION;<br /> 3 l_ret NUMBER;<br /> 4 BEGIN<br /> 5 dbms_lock.sleep(5);<br /> 6 --DBMS_BACKUP_RESTORE.SLEEP(5);<br /> 7 dbms_output.put_line(systimestamp);<br /> 8 SELECT SUM(sal)<br /> 9 INTO l_ret<br /> 10 FROM emp<br /> 11 WHERE deptno = p_deptno;<br /> 12 RETURN l_ret;<br /> 13 END sum2;<br /> 14 /</p><p>Function created</p><p>SQL>