/*
 * Title  : rmn_comp_pkg.sql
 * Author : EXADIME LLC
 * Created: 07-Jul-2025
 * Updated: 07-Jul-2025
 * Release: 1.0.0
 * Purpose: To compile RMAN packages.
 * Comment: Run this after database migration.
 * History: Initial version.
 */

start oracle.ini

set echo off
set feedback on
set serverout on
set termout on

set linesize 200
set pagesize 0
set pause off
set long 80

set heading on
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

clear columns
clear computes

-- Column formatting
column SNO          format 99           heading "#"
column OWNER        format a15          heading "Owner"
column OBJECT_NAME  format a30          heading "Package Name"
column OBJECT_TYPE  format a15          heading "Type"
column STATUS       format a10          heading "Status"
column LAST_DDL     format a19          heading "Last DDL Time"
column COMPILE_CMD  format a60          heading "Compile Command"

spool &&rpt_dir/rmn_comp_pkg.rpt

prompt
prompt RMAN Package Compilation Status
prompt ================================
prompt

-- Show current status of RMAN packages before compilation
SELECT
    ROWNUM AS SNO,
    OWNER,
    OBJECT_NAME,
    OBJECT_TYPE,
    STATUS,
    TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL
FROM 
    DBA_OBJECTS
WHERE
    OWNER = 'SYS'
    AND OBJECT_NAME LIKE '%RMAN%'
    AND OBJECT_TYPE IN ('PACKAGE', 'PACKAGE BODY')
ORDER BY
    OBJECT_NAME,
    OBJECT_TYPE;

prompt
prompt Compiling RMAN Packages...
prompt ==========================
prompt

-- Execute RMAN package compilation scripts in correct order
prompt Running RMAN Package Compilation Scripts...
prompt

prompt 1. Running dbmsrman.sql...
prompt ===========================
@@$ORACLE_HOME/rdbms/admin/dbmsrman.sql

prompt
prompt 2. Running dbmsbkrs.sql...
prompt ==========================
@@$ORACLE_HOME/rdbms/admin/dbmsbkrs.sql

prompt
prompt 3. Running prvtrmns.plb...
prompt ==========================
@@$ORACLE_HOME/rdbms/admin/prvtrmns.plb

prompt
prompt 4. Running prvtbkrs.plb...
prompt ==========================
@@$ORACLE_HOME/rdbms/admin/prvtbkrs.plb

prompt
prompt RMAN Package Compilation Scripts Completed.
prompt

prompt
prompt Post-Compilation Status
prompt ========================
prompt

-- Show status after compilation
SELECT
    ROWNUM AS SNO,
    OWNER,
    OBJECT_NAME,
    OBJECT_TYPE,
    STATUS,
    TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL
FROM 
    DBA_OBJECTS
WHERE
    OWNER = 'SYS'
    AND OBJECT_NAME LIKE '%RMAN%'
    AND OBJECT_TYPE IN ('PACKAGE', 'PACKAGE BODY')
ORDER BY
    OBJECT_NAME,
    OBJECT_TYPE;

prompt
prompt Compilation Summary
prompt ===================
prompt

-- Summary of compilation results
SELECT 
    'Total RMAN Packages: ' || COUNT(*) AS SUMMARY_INFO
FROM 
    DBA_OBJECTS
WHERE
    OWNER = 'SYS'
    AND OBJECT_NAME LIKE '%RMAN%'
    AND OBJECT_TYPE IN ('PACKAGE', 'PACKAGE BODY')
UNION ALL
SELECT 
    'Valid Packages: ' || COUNT(*)
FROM 
    DBA_OBJECTS
WHERE
    OWNER = 'SYS'
    AND OBJECT_NAME LIKE '%RMAN%'
    AND OBJECT_TYPE IN ('PACKAGE', 'PACKAGE BODY')
    AND STATUS = 'VALID'
UNION ALL
SELECT 
    'Invalid Packages: ' || COUNT(*)
FROM 
    DBA_OBJECTS
WHERE
    OWNER = 'SYS'
    AND OBJECT_NAME LIKE '%RMAN%'
    AND OBJECT_TYPE IN ('PACKAGE', 'PACKAGE BODY')
    AND STATUS = 'INVALID';

-- Show any remaining compilation errors
prompt
prompt Compilation Errors (if any)
prompt ============================
prompt

SELECT
    OWNER,
    NAME,
    TYPE,
    LINE,
    POSITION,
    TEXT AS ERROR_TEXT
FROM 
    DBA_ERRORS
WHERE
    OWNER = 'SYS'
    AND NAME LIKE '%RMAN%'
    AND TYPE IN ('PACKAGE', 'PACKAGE BODY')
ORDER BY
    NAME,
    TYPE,
    SEQUENCE;

spool off

clear columns

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80
set long 80

set termout on
set serverout off
set feedback on
set echo off
