Shulou(Shulou.com)05/31 Report--

This article focuses on "what is the invalid recompiled stored procedure in Oracle". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are invalid stored procedures in Oracle?"

Stored procedures in Oracle become invalid in some cases, and a small red fork is displayed in the upper-left corner of the stored procedure icon in PL/SQL Developer. For example, the invalidation of the object referenced by the storage process and the failure of the dblink may cause the stored procedure used to invalidate. In addition, my stored procedures often become invalid, and so far the reason has not been identified.

Query the dba_dependencies view to see the objects referenced by the stored procedure, and then in the dba_objects view you can see the created and last_ddl_time times of the objects.

As long as there is no grammatical problem with the invalid stored procedure above, a recompile is available again. You can't manually compile every time you find it, so there are two ways to automate (all the recompiling of invalid stored procedure code found online in Oracle is confusing, so it's mainly reorganized):

In Oracle SQL * Plus-generate a script file with spool, and then call it in for execution as follows:

Spool ExecCompProc.sql

Select 'alter procedure' | | object_name | | 'compile;' from all_objects

Where status = 'INVALID' and object_type =' PROCEDURE' AND owner='UNMI'

Spool off

@ ExecCompProc.sql

Write as a stored procedure-make the stored procedure execute at some point, such as in Job, with the following code:

Create or replace procedure compile_invalid_procedures (

P_owner varchar2-owner name, that is, SCHEMA

) as

-- compile an invalid stored procedure under a user

Str_sql varchar2 (200)

Begin

For invalid_procedures in (select object_name from all_objects

Where status = 'INVALID' and object_type =' PROCEDURE' and owner=upper (p_owner))

Loop

Str_sql: = 'alter procedure' | | invalid_procedures.object_name | | 'compile'

Begin

Execute immediate str_sql

Exception

-- When Others Then Null

When OTHERS Then

Dbms_output.put_line (sqlerrm)

End

End loop

End

What is discussed here is how to recompile stored procedures, in turn, recompile FUNCTION, PACKAGE, TYPE, TRIGGER, etc., and rebuild INDEX, and so on. The difference is that the object_type when querying all_objects is not the same, and the alter statements to be executed are different.

What object_type can be obtained with select distinct object_type from all_objects. For more information on how to write alter, please see the following:

Alter function function_name compile;alter package package) name compile;alter type type_name compile;alter index index_name rebuild; supplement 1. In fact, stored procedures, functions and so on are INVALID, so it doesn't matter as long as there are no errors in the content, because 2. 0 will be automatically recompiled when executed. Using show errors procedure procedure_name or show errors function function_name in the Command Windows of SQL * Plus or PL/SQL Developer, you can see the stored procedure specific error 3. You can use the tools provided by Oracle: dbms_utility.compile_schema (schema varchar2, compile_all boolean default TRUE); to compile all PROCEDURE, FUNCTION, PACKAGE, and TRIGGER under a Schema. For example, execute dbms_utility.compile_schema ('Unmi'). At this point, I believe you have a deeper understanding of "what is an invalid stored procedure in Oracle". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.