關于在procedure中調用dbms_lock的package

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;