oracle存储过程无效是什么意思_oracle存储过程报错继续执行

.[@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-2007Status:PUBLISHEDChecked 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.