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

我在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 declaredORA-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/3PLS-00201: identifier 'OLAP.P_DATETIME1MIN_TMP' must be declared

单独授权(sys用户)：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实现的自动调度也是类似于存储过程的方式来实现的。