Oracle - Compile Packages Procedures Functions
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;
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.
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.
exec DBMS_UTILITY.COMPILE_SCHEMA ('FOO', true ) ;
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.
- to find errors from the compilations do
show errors
select * from dba_errors where owner = 'SYSADM' ;
select * from dba_source where name = 'CUST';
OWNER NAME TYPE LINE TEXT
SYSADM CUST PACKAGE 1 PACKAGE cust wrapped
0
abcd
...
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.