关于在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中的对象，调用时候出现了类似下列的问题。<br> SQL> create or replace procedure proc01 as<br> 2 begin<br> 3 dbms_lock.sleep(10);<br> 4 end;<br> 5 /

procedure created.

Flash Builder操作指南 中文WORD版

本文档主要讲述的是Flash Builder操作指南；Flash Builder将构成应用程序的资源（文件夹和文件）组合到一个容器中，我们将其称为项目。项目包含一组属性，这些属性控制应用程序的构建方式、构建的应用程序所在的位置、调试的处理方式以及该项目于工作空间中其他项目的关系。希望本文档会给有需要的朋友带来帮助；感兴趣的朋友可以过来看看

0

查看详情

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, 感谢原作者分享。