.[@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
Note:1011930.6Type:PROBLEM
Last Revision Date:15-OCT-Status:PUBLISHEDChecked for relevance on 15-Oct-.
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 PILE_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 PILE_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.