Here is a script to recompile invalid objects.
The command to recompile is
alter object_type object_name compile;
The only exception is for the package body. The command to recompile the package body is
alter package package_name compile body;
To take that exception into account, here is the script that will general the "alter" for each invalid database object in the current schema.
select 'alter ' || case when object_type = 'PACKAGE BODY'
then 'PACKAGE'
else object_type
end
|| ' ' || object_name || ' compile ' ||
case when object_type = 'PACKAGE BODY'
then 'body'
else null
end || ';' as mysql
from USER_OBJECTS
where status = 'INVALID';
Hope, it will save you time.
Thanks
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If you are interested in the book "Oracle SQL Scipting". here is the link.
3 comments:
Once you recompile all the database objects using the above script, if there are still invalid objects, you can query the following view to get the detail on the error messages.
SQL> select name, type, text
2 from user_errors;
NAME TYPE TEXT
------------------------------ ------------ ----------------------------------------
P PROCEDURE PL/SQL: SQL Statement ignored
P PROCEDURE PL/SQL: ORA-00942: table or view does
not exist
V VIEW ORA-00904: "TO_TEXT": invalid identifier
There is a another way to compile invalid objects.
SQL> desc dbms_utility
PROCEDURE COMPILE_SCHEMA
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA VARCHAR2 IN
COMPILE_ALL BOOLEAN IN DEFAULT
REUSE_SETTINGS BOOLEAN IN DEFAULT
The following command will compile all the objects (whether valid or invalid) in the current schema.
SQL> exec dbms_utility.compile_schema(user);
If you want to specify the username other than the current schema, you can do it as shown below.
SQL> exec dbms_utility.compile_schema('SCOTT');
PL/SQL procedure successfully completed.
If you want to save some time and only want to compile invalid database objects, use compile_all ==> false. The default value is true.
SQL> exec dbms_utility.compile_schema(user,false);
PL/SQL procedure successfully completed.
Even after running this procedure, there may be some invalid objects in the schema.
Query it using user_errors or dba_errors.
SQL> desc user_errors
Name Null? Type
----------------------------------------------------------------------- -------- -----------------
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(12)
SEQUENCE NOT NULL NUMBER
LINE NOT NULL NUMBER
POSITION NOT NULL NUMBER
TEXT NOT NULL VARCHAR2(4000)
ATTRIBUTE VARCHAR2(9)
MESSAGE_NUMBER NUMBER
nice explanation.. :-)
Post a Comment