马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
本帖最后由 dbaxiaoyu 于 2014-8-13 15:51 编辑
开发人员创建的procedure需要调用dbms_lock的package中的对象,调用时候出现了类似下列的问题。
[AppleScript] 纯文本查看 复制代码 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中则不行了。
[AppleScript] 纯文本查看 复制代码 [/p][p=24, null, left]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在不单独授权的情况下就能够正常调用。
[AppleScript] 纯文本查看 复制代码 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能否实现了:
[AppleScript] 纯文本查看 复制代码 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的示例:
[AppleScript] 纯文本查看 复制代码 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
[url=]<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe[/url]
[url=]
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;
[/url][url=]
D
-
X
[/url] |