关于在procedure中调用dbms_lock的package

开发人员创建的procedure需要调用dbms_lock的package中的对象，调用时候出现了类似下列的问题。 SQL> create or replace procedure proc01 as 2 begin 3 dbms_lock.sleep(10); 4 end; 5 / Procedure created. SQL> show user; USER is "SYS" SQL> conn xiaoy

开发人员创建的procedure需要调用dbms_lock的package中的对象，调用时候出现了类似下列的问题。
SQL> create or replace procedure proc01 as
2 begin
3 dbms_lock.sleep(10);
4 end;
5 /

Procedure created.

SQL> show user;
USER is "SYS"
SQL> conn xiaoyu/xiaoyu
Connected.
SQL> create or replace procedure proc01 as
2 begin
3 dbms_lock.sleep(10);
4 end;
5 /

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE PROC01:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PL/SQL: Statement ignored
3/1 PLS-00201: identifier 'DBMS_LOCK' must be declared
SQL> conn / as sysdba
Connected.

这里用静态sql调用dbms_lock的package时，oracle报出了无法识别该package，这里需要单独授权这个package给用户，这个需要特别注意，因为正常的匿名块程序中我们是可以调用的，但是procedure中则不行了。
SQL> grant execute on sys.dbms_lock to xiaoyu;

Grant succeeded.

SQL> conn xiaoyu/xiaoyu
Connected.
SQL> create or replace procedure proc01 as
2 begin
3 dbms_lock.sleep(10);
4 end;
5 /

Procedure created.

但是这里并不是说所有的dbms开头的package下的对象都不能在procedure或者function中调用，比如dbms_stats的package在不单独授权的情况下就能够正常调用。

SQL> create or replace procedure proc03 as
2 begin
3 dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T_DYNAMIC01');
4 end;
5 /

Procedure created.

这里我们想想动态sql能否实现了：
SQL> revoke execute on sys.dbms_lock from xiaoyu;

Revoke succeeded.

SQL> create or replace procedure proc01 as
2 begin
3 execute immediate 'dbms_lock.sleep(10)';
4 end;
5 /

Procedure created.

SQL> exec proc01;
BEGIN proc01; END;

*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "SYS.PROC01", line 3
ORA-06512: at line 1

这里来看动态sql执行的时候出现了问题，oracle报出了在第三行出现了无效的语句，动态sql相比静态sql可以解决一些静态sql无法实现的问题，比如参数的不确定性造成没办法评估程序的具体操作，再比如在存储过程或者匿名块中实现ddl语句。

如下动态sql解决在procedure中实现ddl的示例：
SQL> create or replace procedure proc02 as
2 begin
3 create table t_dynamic01 as select * from dual;
4 end;
5 /

Warning: Procedure created with compilation errors.

SQL> show errors;
Errors for PROCEDURE PROC02:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PLS-00103: Encountered the symbol "CREATE" when expecting one of
the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe

SQL> create or replace procedure proc02 as
2 begin
3 execute immediate 'create table t_dynamic01 as select * from dual';
4 end;
5 /

Procedure created.

SQL> exec proc02;

PL/SQL procedure successfully completed.

SQL> select * from t_dynamic01;

D
-
X

原文地址：关于在procedure中调用dbms_lock的package, 感谢原作者分享。