/***********************************************************************
 *                                                                     *
 *             Copyright (c) Ultra Electronics Limited 2004            *
 *                         All rights reserved                         *
 *                                                                     *
 ***********************************************************************
 *                                                                     
 * Filename:           compile.sql
 * Module Name:        archive_n_backup
 * Subsystem Name:     Database
 * Originating Author: P.Dawson (from a script supplied by Y.Kogbe)
 * Design Reference:   UAS/BBTRK/CA/774
 *                                                                     
 ***********************************************************************
 *                                                                     
 * Description:
 *
 * SQL Query to generate a script to enable all disabled triggers
 * and to compile all invalid objects FOR THE CURRENT USER ONLY 
 *                                                                     
 ***********************************************************************
 *                                                                     
 * Compile and Link Instructions:                                      
 *                                                                     
 *                     (How to compile and link, listing any special   
 *                     options, libraries etc. Usually only required   
 *                     for stand-alone programs so normally omitted.)  
 *                                                                     
 ***********************************************************************
 * Program Identification Array                                        
 *                                                                     
 * "%Z%%M% VERSION %I% %E% %U%"         
 ***********************************************************************
 * Modification History                                                
 *                                                                     
 * Rel Date/Time      Proj/Id   CA Ref/Details of Change               
 * --- -------------- --------  -------------------------------------- 
 ***********************************************************************/

SET PAGESIZE 0 LINESIZE 120 ECHO OFF FEEDBACK OFF HEADING OFF TERMOUT OFF

SPOOL fix.sql

SELECT 'ALTER '
|| DECODE(object_type,'PACKAGE BODY', 'PACKAGE', 'TYPE BODY', 'TYPE', object_type)
|| ' '
|| LOWER(object_name)
|| ' COMPILE '
|| DECODE(object_type, 'PACKAGE BODY', 'BODY;', ';')
FROM user_objects
WHERE status = 'INVALID'
ORDER BY DECODE(object_type,'PACKAGE BODY', 'ZZZZZ',object_type);

SELECT
'ALTER TRIGGER ' || LOWER(trigger_name) || ' ENABLE;'
FROM user_triggers
WHERE status <> 'ENABLED';

SPOOL OFF
SET ECHO ON FEEDBACK ON HEADING ON TERMOUT ON

START fix
