存储过程调用其他模式的存储过程需要注意的地方

我在fzfw用户下用dbms_job包自动调度了其他2个模式下的存储过程的执行。fzfw是DBA用户。

告警日志报错：
Sun Aug  8 00:00:02 2010
Errors in file /u01/app/oracle/admin/fzfwdb/bdump/fzfwdb1_j001_397952.trc:
ORA-12012: error on auto execute of job 62
ORA-06550: line 1, column 96:
PLS-00201: identifier 'FZFWAPP.P_CLEAR_TMRINDB' must be declared
ORA-06550: line 1, column 96:
PL/SQL: Statement ignored
Sun Aug  8 00:00:02 2010
Errors in file /u01/app/oracle/admin/fzfwdb/bdump/fzfwdb1_j000_676180.trc:
ORA-12012: error on auto execute of job 63
ORA-06550: line 1, column 96:
PLS-00201: identifier 'OLAP.P_DATETIME1MIN_TMP' must be declared
ORA-06550: line 1, column 96:
PL/SQL: Statement ignored

手动在SQLPlus中调用执行(fzfw用户)：
SQL> exec olap.p_datetime1min_tmp;

PL/SQL procedure successfully completed.

 

创建一个测试存储过程(fzfw用户)：
SQL> create or replace procedure p_test
as
begin
  olap.p_datetime1min_tmp;
end p_test;  2    3    4    5
  6  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE P_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: Statement ignored
4/3      PLS-00201: identifier 'OLAP.P_DATETIME1MIN_TMP' must be declared

单独授权(sys用户)：
oracle@bwgl_db1:/home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 9 13:43:00 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> grant execute on olap.p_datetime1min_tmp to fzfw;

Grant succeeded.

SQL> grant execute on fzfwapp.p_clear_tmrindb to fzfw;

Grant succeeded.

再次测试(fzfw用户)：
SQL> create or replace procedure p_test
as
begin
  olap.p_datetime1min_tmp;
end p_test;  2    3    4    5
  6  /

Procedure created.

通过DBMS_JOB再次调度fzfwapp.p_clear_tmrindb,olap.p_datetime1min_tmp这2个存储过程，运行正常。

结论：即使你是DBA用户，你要在存储过程中调用其他模式下的存储过程，必须手动使用grant语句把其他模式的存储过程的执行权限赋予该用户，方可在存储过程中调用其他模式的存储过程。否则就会报PLS-00201错误而无法调用。在这个例子中，我们可以认为dbms_job实现的自动调度也是类似于存储过程的方式来实现的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-670575/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23135684/viewspace-670575/