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

Source: Internet
Author: User

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>

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.