{"id":8656,"date":"2024-05-31T19:01:01","date_gmt":"2024-05-31T11:01:01","guid":{"rendered":""},"modified":"2024-05-31T19:01:01","modified_gmt":"2024-05-31T11:01:01","slug":"oracle\u5b58\u50a8\u8fc7\u7a0b\u65e0\u6548\u662f\u4ec0\u4e48\u610f\u601d_oracle\u5b58\u50a8\u8fc7\u7a0b\u62a5\u9519\u7ee7\u7eed\u6267\u884c","status":"publish","type":"post","link":"https:\/\/mushiming.com\/8656.html","title":{"rendered":"oracle\u5b58\u50a8\u8fc7\u7a0b\u65e0\u6548\u662f\u4ec0\u4e48\u610f\u601d_oracle\u5b58\u50a8\u8fc7\u7a0b\u62a5\u9519\u7ee7\u7eed\u6267\u884c"},"content":{"rendered":"
\n

.[@more@]<\/p>\n

a. \u4fee\u6539\u5b58\u5132\u904e\u7a0b\u8abf\u7528\u7684table\u7d50\u69cb\u6703\u9020\u6210procedure \u9700\u7528\u91cd\u65b0\u7de8\u8b6f (10g \u6703\u81ea\u52d5\u7de8\u8b6f\uff0c\u4e0d\u9700\u8981\u4eba\u70ba) \u3002<\/p>\n

b. \u5b58\u5132\u904e\u7a0b\u8abf\u7528\u53e6\u5916\u4e00\u500b\u5b58\u5132\u904e\u7a0b\uff0c\u53e6\u5916\u4e00\u500b\u4fee\u6539\u4e86\u5176\u4e2d\u7684object\uff0c\u90a3\u4e48\u5169\u500b\u5b58\u5132\u904e\u7a0b\u90fd\u9700\u8981\u91cd\u65b0\u7de8\u8b6f (10g \u6703\u81ea\u52d5\u7de8\u8b6f\uff0c\u4e0d\u9700\u8981\u4eba\u70ba) \u3002<\/p>\n

c. \u5b58\u5132\u904e\u7a0b\u4e2d\u8abf\u7528\u7684table\u5982\u679c\u4ed6\u7684\u540c\u7fa9\u8a5e\u6709\u5176\u4ed6object\u540c\u540d\uff0c\u4fee\u6539\u4e86\u540c\u540d\u7684objects \u4e5f\u6ce8\u610f\u5c0e\u81f4procedure invalid .\u5e94\u5f53\u5c3d\u91cf\u907f\u514d\u521b\u5efa\u4e0ePUBLIC\u540c\u4e49\u8bcd \u540c\u540d\u7684\u5bf9\u8c61<\/p>\n

\u81e8\u6642\u89e3\u6c7a\u65b9\u6cd5\uff1a<\/p>\n

\u628a alter pro_name compile \u8ce6\u7d66\u4e00\u500b\u5b57\u7b26\u4e32\u8b8a\u91cf\uff0c \u7136\u540e\u5728\u7a0b\u5e8f\u4e2d EXECUTE IMMEDIATE (\u985e\u4f3c\u4e8e\u57f7\u884cSQL\u4e00\u6a23) \u3002<\/p>\n

=============================================================<\/p>\n

Subject:ORA-20003: ORU-10036 WHEN CALLING ODESSP() OR DBMS_DESCRIBE.DESCRIBE_PROCEDURE<\/p>\n

Note:1011930.6Type:PROBLEM<\/p>\n

Last Revision Date:15-OCT-2007Status:PUBLISHEDChecked for relevance on 15-Oct-2007.<\/p>\n

Problem Description:<\/p>\n

====================<\/p>\n

You are getting ORA-20003 and ORU-10036 when using the packaged<\/p>\n

procedure DBMS_DESCRIBE.DESCRIBE_PROCEDURE or the Oracle<\/p>\n

Call Interface (OCI) function odessp() to describe the parameters of a<\/p>\n

PL\/SQL stored procedure or function. The associated error message<\/p>\n

is \"object X is invalid and can not be described\".<\/p>\n

Example:<\/p>\n

Here is an example of using DBMS_DESCRIBE.DESCRIBE_PROCEDURE.<\/p>\n

SQL> declare<\/p>\n

2 overload dbms_describe.number_table;<\/p>\n

3 position dbms_describe.number_table;<\/p>\n

4 levl dbms_describe.number_table;<\/p>\n

5 argument_name dbms_describe.varchar2_table;<\/p>\n

6 datatype dbms_describe.number_table;<\/p>\n

7 default_value dbms_describe.number_table;<\/p>\n

8 in_out dbms_describe.number_table;<\/p>\n

9 length dbms_describe.number_table;<\/p>\n

10 precision dbms_describe.number_table;<\/p>\n

11 scale dbms_describe.number_table;<\/p>\n

12 radix dbms_describe.number_table;<\/p>\n

13 spare dbms_describe.number_table;<\/p>\n

14 begin<\/p>\n

15 dbms_describe.describe_procedure(<\/p>\n

16 'P1',<\/p>\n

17 null,<\/p>\n

18 null,<\/p>\n

19 overload,<\/p>\n

20 position,<\/p>\n

21 levl,<\/p>\n

22 argument_name,<\/p>\n

23 datatype,<\/p>\n

24 default_value,<\/p>\n

25 in_out,<\/p>\n

26 length,<\/p>\n

27 precision,<\/p>\n

28 scale,<\/p>\n

29 radix,<\/p>\n

30 spare);<\/p>\n

31<\/p>\n

31 dbms_output.put_line('overload ' ||<\/p>\n

32 'position ' ||<\/p>\n

33 'argument ' ||<\/p>\n

34 'level ' ||<\/p>\n

35 'datatype ' ||<\/p>\n

36 'length ' ||<\/p>\n

37 'prec ' ||<\/p>\n

38 'scale ' ||<\/p>\n

39 'rad ');<\/p>\n

40 dbms_output.put_line('-------------------------' ||<\/p>\n

41 '-------------------------' ||<\/p>\n

42 '-------------------------');<\/p>\n

43 for counter in 1..2 loop<\/p>\n

44 dbms_output.put_line( overload(counter) || ' ' ||<\/p>\n

45 position(counter) || ' ' ||<\/p>\n

46 argument_name(counter) || ' ' ||<\/p>\n

47 levl(counter) || ' ' ||<\/p>\n

48 datatype(counter) || ' ' ||<\/p>\n

49 length(counter) || ' ' ||<\/p>\n

50 precision(counter) || ' ' ||<\/p>\n

51 scale(counter) || ' ' ||<\/p>\n

52 radix(counter) );<\/p>\n

53 end loop;<\/p>\n

54 end;<\/p>\n

55 \/<\/p>\n

declare<\/p>\n

*<\/p>\n

ERROR at line 1:<\/p>\n

ORA-20003: ORU-10036: object P1 is invalid and cannot be described<\/p>\n

ORA-06512: at \"SYS.DBMS_DESCRIBE\", line 83<\/p>\n

ORA-06512: at line 15<\/p>\n

Solution Description:<\/p>\n

=====================<\/p>\n

The status of the procedure or function is INVALID.<\/p>\n

Recompile the procedure\/function.<\/p>\n

The command<\/p>\n

ALTER PROCEDURE COMPILE;<\/p>\n

will recompile a stand-alone stored procedure.<\/p>\n

The command<\/p>\n

ALTER FUNCTION COMPILE;<\/p>\n

will recompile a stand-alone store function.<\/p>\n

The command<\/p>\n

EXECUTE DBMS_UTILITY.COMPILE_SCHEMA();<\/p>\n

will recompile all stored packages, procedures and functions in the<\/p>\n

specified schema. The objects are compiled in dependency oder.<\/p>\n

To see if the status is invalid, check the USER_OBJECTS table.<\/p>\n

Example:<\/p>\n

This example selects procedure P1 from the USER_OBJECTS table.<\/p>\n

As you can see, the status of P1 is INVALID.<\/p>\n

SQL> select * from user_objects where object_name='P1';<\/p>\n

OBJECT_NAME<\/p>\n

------------------------------------------------------------------------------<\/p>\n

--<\/p>\n

OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_<\/p>\n

---------- ------------- --------- ---------<\/p>\n

TIMESTAMP<\/p>\n

---------------------------------------------------------------------------<\/p>\n

STATUS<\/p>\n

-------<\/p>\n

P1<\/p>\n

2114 PROCEDURE 20-SEP-95 20-SEP-95<\/p>\n

1995-09-20:10:11:00<\/p>\n

INVALID<\/p>\n

Solution Explanation:<\/p>\n

=====================<\/p>\n

For more information about the ALTER PROECEDURE or ALTER<\/p>\n

FUNCTION commands, see the Oracle7 Server SQL Reference.<\/p>\n

For more information about DBMS_UTILITY.COMPILE_SCHEMA(),<\/p>\n

see the file dbmsutil.sql. On Unix platforms, this file can be<\/p>\n

found in the $ORACLE_HOME\/rdbms\/admin directory.<\/p>\n

For more information about DBMS_DESCRIBE.DESCRIBE_PROCEDURE()<\/p>\n

see the Oracle7 Server Application Developer's Guide Release 7.2<\/p>\n

pages 6-48 to 6-51. You can also find information in the file<\/p>\n

dbmsdesc.sql which can be found in the $ORACLE_HOME\/rdbms\/admin<\/p>\n

directory on Unix platforms.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"oracle\u5b58\u50a8\u8fc7\u7a0b\u65e0\u6548\u662f\u4ec0\u4e48\u610f\u601d_oracle\u5b58\u50a8\u8fc7\u7a0b\u62a5\u9519\u7ee7\u7eed\u6267\u884c.[@more@]a.\u4fee\u6539\u5b58\u5132\u904e\u7a0b\u8abf\u7528\u7684table\u7d50\u69cb\u6703\u9020\u6210procedure\u9700\u7528\u91cd\u65b0...","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"_links":{"self":[{"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/posts\/8656"}],"collection":[{"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/comments?post=8656"}],"version-history":[{"count":0,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/posts\/8656\/revisions"}],"wp:attachment":[{"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/media?parent=8656"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/categories?post=8656"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mushiming.com\/wp-json\/wp\/v2\/tags?post=8656"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}