ORA-20003 , procedure儲存過程失效問題

.[@more@]

a. 修改儲存過程呼叫的table結構會造成procedure 需用重新編譯 (10g 會自動編譯，不需要人為) 。
b. 儲存過程呼叫另外一個儲存過程，另外一個修改了其中的object，那麼兩個儲存過程都需要重新編譯 (10g 會自動編譯，不需要人為) 。
c. 儲存過程中呼叫的table如果他的同義詞有其他object同名，修改了同名的objects 也注意導致procedure invalid .應當儘量避免建立與PUBLIC同義詞 同名的物件

臨時解決方法：
把 alter pro_name compile 賦給一個字串變數， 然後在程式中 EXECUTE IMMEDIATE (類似於執行SQL一樣) 。

=============================================================

Subject:	ORA-20003: ORU-10036 WHEN CALLING ODESSP() OR DBMS_DESCRIBE.DESCRIBE_PROCEDURE
	Doc ID:	Note:1011930.6	Type:	PROBLEM
	Last Revision Date:	15-OCT-2007	Status:	PUBLISHED

Checked for relevance on 15-Oct-2007.

Problem Description: 
==================== 
 
You are getting ORA-20003 and ORU-10036 when using the packaged 
procedure DBMS_DESCRIBE.DESCRIBE_PROCEDURE or the Oracle  
Call Interface (OCI) function odessp() to describe the parameters of a  
PL/SQL stored procedure or function.  The associated error message  
is "object X is invalid and can not be described". 
 
 
Example: 
 
Here is an example of using DBMS_DESCRIBE.DESCRIBE_PROCEDURE. 
 
SQL> declare 
  2    overload  dbms_describe.number_table; 
  3    position  dbms_describe.number_table; 
  4    levl       dbms_describe.number_table; 
  5    argument_name dbms_describe.varchar2_table; 
  6    datatype  dbms_describe.number_table; 
  7    default_value dbms_describe.number_table; 
  8    in_out  dbms_describe.number_table; 
  9    length  dbms_describe.number_table; 
 10    precision  dbms_describe.number_table; 
 11    scale  dbms_describe.number_table; 
 12    radix  dbms_describe.number_table; 
 13    spare  dbms_describe.number_table; 
 14  begin 
 15    dbms_describe.describe_procedure( 
 16      'P1', 
 17      null, 
 18      null, 
 19      overload, 
 20      position, 
 21      levl, 
 22      argument_name, 
 23      datatype, 
 24      default_value, 
 25      in_out, 
 26      length, 
 27      precision, 
 28      scale, 
 29      radix, 
 30      spare); 
 31   
 31    dbms_output.put_line('overload  ' || 
 32                         'position  ' || 
 33                         'argument  ' || 
 34                         'level  ' || 
 35                         'datatype  ' || 
 36                         'length  ' || 
 37                         'prec  ' || 
 38                         'scale  ' || 
 39                         'rad  '); 
 40    dbms_output.put_line('-------------------------' || 
 41                         '-------------------------' || 
 42                         '-------------------------'); 
 43    for counter in 1..2 loop 
 44      dbms_output.put_line( overload(counter) || '         ' || 
 45                            position(counter) || '         ' || 
 46                            argument_name(counter) || '      ' || 
 47                            levl(counter) || '      ' || 
 48                            datatype(counter) || '         ' || 
 49                            length(counter) || '       ' || 
 50                            precision(counter) || '     ' || 
 51                            scale(counter) || '      ' || 
 52                            radix(counter) ); 
 53    end loop; 
 54  end; 
 55  / 
declare 
 * 
ERROR at line 1: 
ORA-20003: ORU-10036: object P1 is invalid and cannot be described 
ORA-06512: at "SYS.DBMS_DESCRIBE", line 83 
ORA-06512: at line 15


Solution Description: 
===================== 
 
The status of the  procedure or function is INVALID. 
Recompile the procedure/function. 
 
The command 
ALTER PROCEDURE  COMPILE;  
will recompile a stand-alone stored procedure. 
The command 
ALTER FUNCTION  COMPILE; 
will recompile a stand-alone store function. 
The command            
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA();                            
                   
will recompile all stored packages, procedures and functions in the  
specified schema.  The objects are compiled in dependency oder. 
 
To see if the status is invalid, check the USER_OBJECTS table. 
 
Example: 
 
This example selects procedure P1 from the USER_OBJECTS table. 
As you can see, the status of P1 is INVALID. 
 
SQL> select * from user_objects where object_name='P1'; 
 
OBJECT_NAME 
------------------------------------------------------------------------------
-- 
 OBJECT_ID OBJECT_TYPE   CREATED   LAST_DDL_ 
---------- ------------- --------- --------- 
TIMESTAMP 
--------------------------------------------------------------------------- 
STATUS 
------- 
P1 
      2114 PROCEDURE     20-SEP-95 20-SEP-95 
1995-09-20:10:11:00 
INVALID 
 
 
Solution Explanation: 
===================== 
 
For more information about the ALTER PROECEDURE or ALTER  
FUNCTION commands, see the Oracle7 Server SQL Reference. 
 
For more information about DBMS_UTILITY.COMPILE_SCHEMA(), 
see the file dbmsutil.sql.  On Unix platforms, this file can be 
found in the $ORACLE_HOME/rdbms/admin directory. 
 
For more information about DBMS_DESCRIBE.DESCRIBE_PROCEDURE() 
see the Oracle7 Server Application Developer's Guide Release 7.2 
pages 6-48 to 6-51.  You can also find information in the file 
dbmsdesc.sql which can be found in the $ORACLE_HOME/rdbms/admin 
directory on Unix platforms.