Oracle PL/SQL cannot call dbms_lock (grant to role or grant to user) in the function)

Known: The test user tuser1, the test role trole1, and trole1 have been authorized to tuser1.

Latency is required for testing a program, so dbms_lock is authorized to trole1 and put in an anonymous block for testing:

SQL> set serveroutput on; <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 running PM + 08:00 </P> <p> PL/SQL procedure successful </P> <p> SQL>

 

But an error is returned when you move it to the 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

 

It is strange that dbms_lock cannot be used in the function. Google it to find the answer:

You must grant the Execute Permission of dbms_lock to the user directly. If you grant the permission to the role, an error is returned.

Ref: http://forums.oracle.com/forums/thread.jspa? Threadid = 902392 & tstart = 0 & messageid = 3481832

 

Now the function can be created normally, but why? How can there be differences between grant to user and grant to role on some objects?

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>