Re-compile invalid stored procedures in Oracle, 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. There is always no way to manually compile upon each discovery, so there are two ways to automate (all the stored procedures found online in Oracle recompile all invalidCodeThe layout is messy, so it is mainly reorganized ):

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

1. spool execcompproc. SQL
3. select ' alter procedure '| object_name | 'compile; ' from all_objects
4. where Status = 'invalid' and object_type = ' procedure ' and owner = 'unmi ';
6. spool off
8. @ execcompproc. SQL;

Spool execcompproc. SQL <br/> select 'alter procedure '| object_name | 'compile; 'From all_objects <br/> where status = 'invalid' and object_type = 'processed' and owner = 'unmi'; <br/> spool off <br/> @ 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:

2. Create Or Replace ProcedureCompile_invalid_procedures (
4. P_owner varchar2-- Owner name, that is, schema
6. )As
10. -- Compile invalid stored procedures for a user
14. Str_ SQL varchar2 (200 );
18. Begin
20. ForInvalid_proceduresIn(SelectObject_nameFromAll_objects
22. WhereStatus = 'invalid'AndObject_type ='Procedure'AndOwner =Upper(P_owner ))
24. Loop
26. Str_ SQL: ='Alter Procedure'| Invalid_procedures.object_name | 'compile ';
28. Begin
30. ExecuteImmediate str_ SQL;
32. Exception
34. -- When others then NULL;
36. WhenOthersThen
38. Dbms_output.put_line (sqlerrm );
40. End;
42. EndLoop;
44. End;

Create or replace procedure compile_invalid_procedures (<br/> p_owner varchar2 -- owner name, schema <br/>) as <br/> -- compile an invalid stored procedure for a user <br/> str_ SQL varchar2 (200 ); <br/> begin <br/> for invalid_procedures in (select object_name from all_objects <br/> where status = 'invalid' and object_type = 'processed' and owner = upper (p_owner)) <Br/> loop <br/> str_ SQL: = 'alter procedure '| invalid_procedures.object_name | 'compile'; <br/> begin <br/> execute immediate str_ SQL; <br/> exception <br/> -- when others then NULL; <br/> when others then <br/> dbms_output.put_line (sqlerrm); <br/> end; <br/> end loop; <br/> end;
If You Want To See dbms_output.put_line (sqlerrm) when running the stored procedure in SQL * Plus ); set serverout on must be executed 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:

1. Alter FunctionFunction_name compile;
2. AlterPackage package)NameCompile;
3. AlterType type_name compile;
4. Alter IndexIndex_name rebuild;
5. -- Wait ............

Alter function function_name compile; <br/> alter package) Name compile; <br/> alter type type_name compile; <br/> alter index index_name rebuild; <br/> -- wait ............

Reference: 1. Why does my stored procedure always need to be re-compiled?
2. recompile all invalid stored procedures in Oracle

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 ').