{"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":"<div style=\"font-size:16px;\">\n<p>.[@more@]<\/p>\n<p>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<p>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<p>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<p>\u81e8\u6642\u89e3\u6c7a\u65b9\u6cd5\uff1a<\/p>\n<p>\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>=============================================================<\/p>\n<p>Subject:ORA-20003: ORU-10036 WHEN CALLING ODESSP() OR DBMS_DESCRIBE.DESCRIBE_PROCEDURE<\/p>\n<p>Note:1011930.6Type:PROBLEM<\/p>\n<p>Last Revision Date:15-OCT-2007Status:PUBLISHEDChecked for relevance on 15-Oct-2007.<\/p>\n<p>Problem Description:<\/p>\n<p>====================<\/p>\n<p>You are getting ORA-20003 and ORU-10036 when using the packaged<\/p>\n<p>procedure DBMS_DESCRIBE.DESCRIBE_PROCEDURE or the Oracle<\/p>\n<p>Call Interface (OCI) function odessp() to describe the parameters of a<\/p>\n<p>PL\/SQL stored procedure or function. The associated error message<\/p>\n<p>is \"object X is invalid and can not be described\".<\/p>\n<p>Example:<\/p>\n<p>Here is an example of using DBMS_DESCRIBE.DESCRIBE_PROCEDURE.<\/p>\n<p>SQL&gt; declare<\/p>\n<p>2 overload dbms_describe.number_table;<\/p>\n<p>3 position dbms_describe.number_table;<\/p>\n<p>4 levl dbms_describe.number_table;<\/p>\n<p>5 argument_name dbms_describe.varchar2_table;<\/p>\n<p>6 datatype dbms_describe.number_table;<\/p>\n<p>7 default_value dbms_describe.number_table;<\/p>\n<p>8 in_out dbms_describe.number_table;<\/p>\n<p>9 length dbms_describe.number_table;<\/p>\n<p>10 precision dbms_describe.number_table;<\/p>\n<p>11 scale dbms_describe.number_table;<\/p>\n<p>12 radix dbms_describe.number_table;<\/p>\n<p>13 spare dbms_describe.number_table;<\/p>\n<p>14 begin<\/p>\n<p>15 dbms_describe.describe_procedure(<\/p>\n<p>16 'P1',<\/p>\n<p>17 null,<\/p>\n<p>18 null,<\/p>\n<p>19 overload,<\/p>\n<p>20 position,<\/p>\n<p>21 levl,<\/p>\n<p>22 argument_name,<\/p>\n<p>23 datatype,<\/p>\n<p>24 default_value,<\/p>\n<p>25 in_out,<\/p>\n<p>26 length,<\/p>\n<p>27 precision,<\/p>\n<p>28 scale,<\/p>\n<p>29 radix,<\/p>\n<p>30 spare);<\/p>\n<p>31<\/p>\n<p>31 dbms_output.put_line('overload ' ||<\/p>\n<p>32 'position ' ||<\/p>\n<p>33 'argument ' ||<\/p>\n<p>34 'level ' ||<\/p>\n<p>35 'datatype ' ||<\/p>\n<p>36 'length ' ||<\/p>\n<p>37 'prec ' ||<\/p>\n<p>38 'scale ' ||<\/p>\n<p>39 'rad ');<\/p>\n<p>40 dbms_output.put_line('-------------------------' ||<\/p>\n<p>41 '-------------------------' ||<\/p>\n<p>42 '-------------------------');<\/p>\n<p>43 for counter in 1..2 loop<\/p>\n<p>44 dbms_output.put_line( overload(counter) || ' ' ||<\/p>\n<p>45 position(counter) || ' ' ||<\/p>\n<p>46 argument_name(counter) || ' ' ||<\/p>\n<p>47 levl(counter) || ' ' ||<\/p>\n<p>48 datatype(counter) || ' ' ||<\/p>\n<p>49 length(counter) || ' ' ||<\/p>\n<p>50 precision(counter) || ' ' ||<\/p>\n<p>51 scale(counter) || ' ' ||<\/p>\n<p>52 radix(counter) );<\/p>\n<p>53 end loop;<\/p>\n<p>54 end;<\/p>\n<p>55 \/<\/p>\n<p>declare<\/p>\n<p>*<\/p>\n<p>ERROR at line 1:<\/p>\n<p>ORA-20003: ORU-10036: object P1 is invalid and cannot be described<\/p>\n<p>ORA-06512: at \"SYS.DBMS_DESCRIBE\", line 83<\/p>\n<p>ORA-06512: at line 15<\/p>\n<p>Solution Description:<\/p>\n<p>=====================<\/p>\n<p>The status of the procedure or function is INVALID.<\/p>\n<p>Recompile the procedure\/function.<\/p>\n<p>The command<\/p>\n<p>ALTER PROCEDURE COMPILE;<\/p>\n<p>will recompile a stand-alone stored procedure.<\/p>\n<p>The command<\/p>\n<p>ALTER FUNCTION COMPILE;<\/p>\n<p>will recompile a stand-alone store function.<\/p>\n<p>The command<\/p>\n<p>EXECUTE DBMS_UTILITY.COMPILE_SCHEMA();<\/p>\n<p>will recompile all stored packages, procedures and functions in the<\/p>\n<p>specified schema. The objects are compiled in dependency oder.<\/p>\n<p>To see if the status is invalid, check the USER_OBJECTS table.<\/p>\n<p>Example:<\/p>\n<p>This example selects procedure P1 from the USER_OBJECTS table.<\/p>\n<p>As you can see, the status of P1 is INVALID.<\/p>\n<p>SQL&gt; select * from user_objects where object_name='P1';<\/p>\n<p>OBJECT_NAME<\/p>\n<p>------------------------------------------------------------------------------<\/p>\n<p>--<\/p>\n<p>OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_<\/p>\n<p>---------- ------------- --------- ---------<\/p>\n<p>TIMESTAMP<\/p>\n<p>---------------------------------------------------------------------------<\/p>\n<p>STATUS<\/p>\n<p>-------<\/p>\n<p>P1<\/p>\n<p>2114 PROCEDURE 20-SEP-95 20-SEP-95<\/p>\n<p>1995-09-20:10:11:00<\/p>\n<p>INVALID<\/p>\n<p>Solution Explanation:<\/p>\n<p>=====================<\/p>\n<p>For more information about the ALTER PROECEDURE or ALTER<\/p>\n<p>FUNCTION commands, see the Oracle7 Server SQL Reference.<\/p>\n<p>For more information about DBMS_UTILITY.COMPILE_SCHEMA(),<\/p>\n<p>see the file dbmsutil.sql. On Unix platforms, this file can be<\/p>\n<p>found in the $ORACLE_HOME\/rdbms\/admin directory.<\/p>\n<p>For more information about DBMS_DESCRIBE.DESCRIBE_PROCEDURE()<\/p>\n<p>see the Oracle7 Server Application Developer's Guide Release 7.2<\/p>\n<p>pages 6-48 to 6-51. You can also find information in the file<\/p>\n<p>dbmsdesc.sql which can be found in the $ORACLE_HOME\/rdbms\/admin<\/p>\n<p>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}]}}