Oracle SQL Script to Compile All Package, Body in a Schema

advertisements

_____________________________________________________________________________________________________________________

This script will compile all the Package and package body in the schema. If you wanted to compile all the packages in the all schema, change the user_objects to dba_objects and you have to connect as a DBA privileged user.

1. Create a sql file and copy below script.
2. Connect to sqlplus with the username where you wanted to compile all packages.

SET PAGES 0

SET LINES 120

SET FEEDBACK OFF

SET ECHO OFF

SPOOL COMP_PACKAGE.sql

SELECT 'SET PAGES 0 LINES 120 FEEDBACK ON ECHO ON' FROM DUAL;

SELECT 'SPOOL COMP_PACKAGE.log' from dual;

select 'alter package '||object_name||decode(object_type,'PACKAGE',' compile;',' compile body;')

from   user_objects

where   object_type in ('PACKAGE','PACKAGE BODY')

order by object_type;

SELECT 'SPOOL OFF' from dual;

SPOOL OFF

@COMP_PACKAGE.sql

_____________________________________________________________________________________________________________________

Website Stats