How to compile Invalid Object?

Mohammad taj's picture
articles: 

There are five ways to recompile invalid objects in schema.

1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL
5. Manually Recompile


DBMS_DDL.ALTER_COMPILE


Definition

This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]

Syntax

Exec dbms_ddl.alter_compile ( type , schema, name);
Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
Schema : Database Username

Name : Objects name

Example

SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');

PL/SQL procedure successfully completed.


DBMS_UTILITY.COMPILE_SCHEMA


Definition

This procedure compiles all procedures, functions, packages, and triggers in the specified schema.

Syntax

Exec dbms_utility.compile_schema ( schema,compile all)

Schema : Database Username

Compile All : Object type ( procedure, function, packages,trigger)

Example

SQL> exec dbms_utility.compile_schema('SCOTT');

PL/SQL procedure successfully completed.


UTL_RECOMP


Definition

This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects.

Syntax

Exec UTL_RECOMP.RECOMP_SERIAL ();

Example

SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();

PL/SQL procedure successfully completed.


Note: Required SYS user to run this package.


UTLRP.SQL scripts


Definition

Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.

Syntax
Located: $ORACLE_HOME/rdbms/admin

Example
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\UTLRP.SQL

TIMESTAMP
-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2007-08-04 12:47:21


PL/SQL procedure successfully completed.


TIMESTAMP
-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END 2007-08-04 12:47:26


PL/SQL procedure successfully completed.


Note: Required SYS user to run this script.
Recommended: After upgrade or migrate database.


Best Approach is manually recompiling all Invalid Objects

Spool recompile.sql

Select ‘alter ‘object_type’ ’object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And object_type IN (‘VIEW’,’SYNONYM’,
‘PROCEDURE’,’FUNCTION’,
‘PACKAGE’,’TRIGGER’);

Spool off
@recompile.sql


Note: VIEW,SYNONYM,PROCEDURE,PACKAGE,FUNCTION,TRIGGER


Spool pkg_body.sql

Select ‘alter package ’object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type = ‘PACKAGE BODY’;

Spool off
@pkg_body.sql


Spool undefined.sql

select ‘alter materizlized view ’object_name’ compile;’
From user_objects
where status <> ‘VALID’
And object_type =‘UNDEFINED’;

Spool off
@undefined.sql


Spool javaclass.sql

Select ‘alter java class ’object_name’ resolve;’
from user_objects
where status <> ‘VALID’
And object_type =‘JAVA CLASS’;

Spool off
@javaclass.sql


Spool typebody.sql

Select ‘alter type ‘object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type =‘TYPE BODY’;

Spool off
@typebody.sql


Spool public_synonym.sql

Select ‘alter public synonym ‘object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And owner = ‘PUBLIC’
And object_type = ‘SYNONYM’;

Spool off
@public_synonym.sql



Objects need to recompile are:
VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED (MATERIALIZED VIEW), JAVA CLASS, TYPE, TYPE BODY

http://dbataj.blogspot.com/2007/08/how-to-compile-invalid-objects.html

Comments


Hi,

Very useful article but full of syntax errors & spell checks req.

This seems very useful, but since has many syntax errors would led readers not getting the proper idea.

Thanks,
Priya.

Priya,

Could you, please, point us (and the author) to some of the "many syntax errors"?

Error is comment;Use you

Syntax
Located: $ORACLE_HOME/rdbms/admin

Example
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\UTLRP.SQL

TIMESTAMP
-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2007-08-04 12:47:21

PL/SQL procedure successfully completed.

TIMESTAMP
-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END 2007-08-04 12:47:26

PL/SQL procedure successfully completed.

Note: Required SYS user to run this script.
Recommended: After upgrade or migrate database.

Mohammad taj's picture

Priya,
There is no syntax error, becuase whenever i post anything on my blog first i tested and post.
It might be possible your talking about quotes.

becuase your trying to just copy and paste above SQL syntax and your sure getting below error

SQL> Select `alter public synonym `object_name' compile;'
2 From user_objects
3 Where status <> `VALID'
4 And owner = `PUBLIC'
5 And object_type = `SYNONYM';
ERROR:
ORA-01756: quoted string not properly terminated

So kindly correct starting "single quotes".

Best Regards
Mohammed Taj
http://dbataj.blogspot.com
http://tech.groups.yahoo.com/group/oracleclub/

I was unable to execute the manual sql. Here is what I used. Above was helpful as a starting point:

Spool recompile.sql

Select 'alter '||owner||'.'||object_type||' '||object_name||' compile;' From dba_objects
Where status <> 'VALID'
And object_type IN ('VIEW','SYNONYM',
'PROCEDURE','FUNCTION',
'PACKAGE','TRIGGER');

Spool off
@recompile.sql

Note: VIEW,SYNONYM,PROCEDURE,PACKAGE,FUNCTION,TRIGGER

Spool pkg_body.sql

Select 'alter package '||owner||'.'||object_name||' compile body;' From dba_objects
where status <> 'VALID'
And object_type = 'PACKAGE BODY';

Spool off
@pkg_body.sql

Spool undefined.sql

select 'alter materizlized view '||owner||'.'||object_name||' compile;' From dba_objects
where status <> 'VALID'
And object_type ='UNDEFINED';

Spool off
@undefined.sql

Spool javaclass.sql

Select 'alter java class '||owner||'.'||object_name||' resolve;' from dba_objects
where status <> 'VALID'
And object_type ='JAVA CLASS';

Spool off
@javaclass.sql

Spool typebody.sql

Select 'alter type '||owner||'.'||object_name||' compile body;' From dba_objects
where status <> 'VALID'
And object_type ='TYPE BODY';

Spool off
@typebody.sql

Spool public_synonym.sql

Select 'alter public synonym '||object_name||' compile;' From dba_objects
Where status <> 'VALID'
And owner = 'PUBLIC'
And object_type = 'SYNONYM';

Spool off
@public_synonym.sql

Hi,

I have around 200 procedures and packages in my database. I'm using PL/SQL developer.

Whenever I add a column in any table, many procedures go to un-compiled state. Hence, I have to manually open and recompile each of the procedures and also recompile the referencing objects for each procedure/package.

My problem is mainly to recompile referencing objects. Is there any syntax for this? Is there any other easier way to do this?

Very informative. Thank you.

Regards,
Johnny.

Informative article.