Oracle - Compile Packages Procedures Functions

Contents

Overview

If some code needs recompilation it done automatically recompiled when they are executed. You can use the ALTER PACKAGE statement to explicitly recompile a package specification, body, or both. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead. You can also compile different object types like views, triggers, synonyms, packages, functions, procedures 'instead of recreating them.
Prerequisite: In oder to compile you need the following grants.
   grant select on dba_tab_cols

   grant select on dba_contraints;

Compile Objects in an Oracle DB

All kind of object types can be compiled
 alter procedure pro_util compile ;

 alter function fun_util compile ;

 alter trigger tr_bch_trace compile ;

 alter view dab_view compile ;

 alter package dab_util compile ;

 alter package dab_util compile body;

 alter package dab_util compile package;

 alter view dab_view compile;

 alter public synonym ‘object_name' compile

 EXEC DBMS_UTILITY.compile_schema(schema => 'AHAADM');
 PL/SQL procedure successfully completed.


Compile Invalid Objects


  alter package FOO.SOO_PKG compile package;

Because all objects in a package are stored as a unit, the ALTER PACKAGE statement recompile all package objects together.
NOTE: You cannot use the ALTER PROCEDURE statement or ALTER FUNCTION statement to recompile individually a procedure or function that is part of a package.


Compile Schema

   exec  DBMS_UTILITY.COMPILE_SCHEMA ('FOO', true ) ;

Two Phase Compilation of Invalid Objects

Generate Statements

  spool compile_invalid_objects.sql
  SELECT 'alter package ' || owner || '.' || object_name || ' compile package;' 
  FROM dba_objects 
  WHERE status <> 'VALID'  and object_type like 'PACKAGE%'
  spool off;

NOTE: For you to modify a package, the package must be in your own schema or you must have ALTER ANY PROCEDURE system privilege.

Diagnostics and Handle Compilation Errors


   select * from dba_errors where owner = 'SYSADM' ;

   select owner,object_name, object_type, status 
   from dba_objects where object_name in ('CUST', 'CONTRACT') order by 2;

   OWNER	OBJECT_NAME	OBJECT_TYPE	STATUS
   SYSADM	CONTRACT	PACKAGE	VALID
   SYSADM	CONTRACT	PACKAGE BODY	VALID <------------------
   SYSADM	CUST	        PACKAGE	VALID




See Also:
Oracle-Handle-Invalid-Objects
Oracle-System-Views

Status: Published Date: 2017/06/05 14:13:06 Revision: 1.1

Copyright bei Andreas Haack (C) 2014. Diese Seite wird so wie sie ist zur Verfuegung gestellt, ohne irgenweche Garantien der Verwendbarkeit fuer bestimte Zwecke. Die auf dieser Seiten angebrachten Links liegen ausserhalb der redaktionellen Verantwortung von Andreas Haack und es wird keine Haftung oder Garantie uebernommen. Die Seiten sind Copyright (c) 2014 von Andreas Haack. Kein Teil darf ohne die schriftliche Einverstaendnis von Andreas Haack veroeffentlicht werden.
The page is provided 'as is' , without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fit- ness for a particular purpose and non-infringement. In no event shall Andreas Haack be liable for any claim, damages or other liability. This page is copyrighted property of Andreas Haack. Copyright by Andreas Haack (c) 2014 . No part of this page may be published without written permission for Andreas Haack. A hyper-link may created to this page but NOT to the embedded elements of this page. It may be freely downloaded for private purpose only as long as it is unaltered.