Oracle re-Compilation of invalid stored procedures, or functions, triggers, and other objects

In some cases, the stored procedure in Oracle becomes invalid. A Red Cross is displayed in the upper left corner of the stored procedure icon in PL/SQL Developer. For example, if the objects referenced in the stored procedure are invalid, dblink may fail to use the stored procedure. In addition, my stored procedures often become invalid, and the reasons have not been found out yet.

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

The above invalid stored procedure is available as long as it is not a syntax problem. It cannot be manually compiled every time it is detected, So automation is required, there are two ways to recompile all the invalid Stored Procedure Code found on the Internet in Oracle. The layout is messy, so it is mainly to be reorganized ):

1. in Oracle SQL * Plus-use spool to generate a script file and then @ call it for execution. The Code is as follows:

Spool ExecCompProc. SQL

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

Where status = 'invalid' and object_type = 'processed' AND owner = 'unmi ';

Spool off

@ ExecCompProc. SQL;

2. Write a stored procedure-execute the stored procedure at a certain time, such as in a Job. The Code is as follows:

Create or replace procedure compile_invalid_procedures (

P_owner varchar2 -- owner name, that is, SCHEMA

)

-- Compile invalid stored procedures for a user

Str_ SQL varchar2 (200 );

Begin

For invalid_procedures in (select object_name from all_objects

Where status = 'invalid' and object_type = 'processed' 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;

When running this stored procedure in SQL * Plus, if you want to see the output of dbms_output.put_line (sqlerrm);, You need to execute set serverout on to open the output.

Here we will explain how to re-compile the stored procedure, push and re-compile the FUNCTION, PACKAGE, TYPE, TRIGGER, and INDEX. The difference is that the object_type is different when all_objects is queried, And the alter statement to be executed is different.

Object_type can be obtained from select distinct object_type from all_objects. For the alter statement writing method, refer to the following:

Alter function function_name compile;

Alter package) name compile;

Alter type type_name compile;

Alter index index_name rebuild;

-- Wait ............

For more information, see:

1. In fact, the stored procedure and function are INVALID. It doesn't matter if the content is correct, because it will be automatically re-compiled during execution.

2. Use show errors procedure procedure_name or show errors function function_name in Command Windows of SQL * Plus or PL/SQL Developer to view the specific error of the stored procedure.

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 in a Schema. for example, run dbms_utility.compile_schema ('unmi ').