儲存過程呼叫其他模式的儲存過程需要注意的地方

我在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使用者)：
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實現的自動排程也是類似於儲存過程的方式來實現的。