WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT


SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET LINESIZE 1000
SET TRIMSPOOL ON

-- Define OWNER parameter (will be replaced by PowerShell)
DEFINE OWNER = &1

-- Debug: Check what objects exist for the specified owner
SELECT 'DEBUG: Total objects for owner ' || '&OWNER' || ': ' || COUNT(*) FROM all_objects WHERE owner = UPPER('&OWNER');

-- Extract tables
SELECT 'TABLE' || ',' || table_name 
    FROM all_tables 
    WHERE owner = UPPER('&OWNER') 
        AND table_name IS NOT NULL 
        AND temporary = 'N' 
        AND table_name NOT IN (SELECT object_name FROM all_objects WHERE owner = UPPER('&OWNER') AND object_type = 'MATERIALIZED VIEW')
        AND table_name NOT IN (SELECT table_name FROM dba_lobs WHERE owner = UPPER('&OWNER'))
        AND EXISTS (SELECT 1 FROM all_objects ao WHERE ao.owner = all_tables.owner AND ao.object_name = all_tables.table_name AND ao.object_type = 'TABLE' AND ao.status = 'VALID')
    ORDER BY table_name;

-- Extract tables
SELECT 'TABLE_LOB' || ',' || table_name 
    FROM all_tables 
    WHERE owner = UPPER('&OWNER') 
        AND table_name IS NOT NULL 
        AND temporary = 'N' 
        AND table_name NOT IN (SELECT object_name FROM all_objects WHERE owner = UPPER('&OWNER') AND object_type = 'MATERIALIZED VIEW')
        AND table_name IN (SELECT table_name FROM dba_lobs WHERE owner = UPPER('&OWNER'))
        AND EXISTS (SELECT 1 FROM all_objects ao WHERE ao.owner = all_tables.owner AND ao.object_name = all_tables.table_name AND ao.object_type = 'TABLE' AND ao.status = 'VALID')
    ORDER BY table_name;

-- Extract materialized views
SELECT 'VIEW' || ',' || mview_name 
    FROM all_mviews 
    WHERE owner = UPPER('&OWNER')
        AND EXISTS (SELECT 1 FROM all_objects ao WHERE ao.owner = all_mviews.owner AND ao.object_name = all_mviews.mview_name AND ao.object_type = 'MATERIALIZED VIEW' AND ao.status = 'VALID');

-- Extract temp tables
SELECT 'GLOBAL TEMPORARY TABLE' || ',' || table_name 
    FROM all_tables 
    WHERE owner = UPPER('&OWNER') 
        AND temporary = 'Y'
        AND EXISTS (SELECT 1 FROM all_objects ao WHERE ao.owner = all_tables.owner AND ao.object_name = all_tables.table_name AND ao.object_type = 'TABLE' AND ao.status = 'VALID');

-- Extract views
SELECT 'VIEW' || ',' || view_name 
    FROM all_views 
    WHERE owner = UPPER('&OWNER') 
        AND view_name IS NOT NULL
        AND EXISTS (SELECT 1 FROM all_objects ao WHERE ao.owner = all_views.owner AND ao.object_name = all_views.view_name AND ao.object_type = 'VIEW' AND ao.status = 'VALID');

-- Extract sequences
SELECT 'SEQUENCE' || ',' || sequence_name 
    FROM all_sequences 
    WHERE sequence_owner = UPPER('&OWNER') 
        AND sequence_name IS NOT NULL
        AND EXISTS (SELECT 1 FROM all_objects ao WHERE ao.owner = all_sequences.sequence_owner AND ao.object_name = all_sequences.sequence_name AND ao.object_type = 'SEQUENCE' AND ao.status = 'VALID');

-- Extract packages
SELECT 'PACKAGE' || ',' || object_name 
    FROM all_objects 
    WHERE owner = UPPER('&OWNER') 
        AND object_type = 'PACKAGE' 
        AND object_name IS NOT NULL
        AND status = 'VALID';

-- Extract functions
SELECT 'FUNCTION' || ',' || object_name 
    FROM all_objects 
    WHERE owner = UPPER('&OWNER') 
        AND object_type = 'FUNCTION' 
        AND object_name IS NOT NULL
        AND status = 'VALID';

-- Extract procedures
SELECT 'PROCEDURE' || ',' || object_name 
    FROM all_objects 
    WHERE owner = UPPER('&OWNER') 
        AND object_type = 'PROCEDURE' 
        AND object_name IS NOT NULL
        AND status = 'VALID';

-- Extract type
SELECT 'TYPE' || ',' || object_name 
    FROM all_objects 
    WHERE owner = UPPER('&OWNER') 
        AND object_type = 'TYPE' 
        AND object_name IS NOT NULL
        AND status = 'VALID';

-- Extract Trigger (excluding disabled triggers)
SELECT 'TRIGGER' || ',' || TRIGGER_NAME 
    FROM ALL_TRIGGERS 
    WHERE owner = UPPER('&OWNER') 
        AND TRIGGER_NAME IS NOT NULL 
        AND status = 'ENABLED';

EXIT;
