DOITLIKEITSYOURJOB - Remove Duplicates

Remove Duplicates

Creation of Logging Table (DUPLICATE_CLEANUP_LOG): This logs any errors during the cleanup.
Creation of the Backup Table: The backup table will be dynamically created for each table being cleaned.
Procedure to Check, Backup, and Optionally Delete Duplicates: Encapsulated within a package for reuse.

Optional Testing: You can uncomment the example usage in the script to run a test directly.

This version allows you to handle duplicates in a safe, reusable, and error-tracked manner across different tables and schemas. Let me know if you need any further adjustments!

V3

DECLARE v_where_clause CLOB := ''; -- Changed from VARCHAR2 to CLOB for larger size p_unique_columns CLOB := ''; -- Changed from VARCHAR2 to CLOB for larger sizeBEGIN -- Create a list of unique columns, excluding 'UUID' and 'PROCESSED_TS' FOR col IN (SELECT column_name, data_type FROM all_tab_columns WHERE owner = UPPER('DGSTREAM') AND table_name = UPPER('LK_CREDIT_AGREEMENT') AND column_name NOT IN ('UUID', 'PROCESSED_TS') ORDER BY column_id) LOOP IF LENGTH(p_unique_columns) > 0 THEN p_unique_columns := p_unique_columns || ', '; END IF; p_unique_columns := p_unique_columns || col.column_name; -- Dynamically add each column comparison to the WHERE clause based on data type IF col.data_type LIKE '%CHAR%' OR col.data_type = 'NCHAR' THEN -- For character columns, use NVL with an empty string v_where_clause := v_where_clause || ' AND COALESCE(A."' || col.column_name || '", ''NULL'') = COALESCE(B."' || col.column_name || '", ''NULL'')'; ELSIF col.data_type = 'NUMBER' OR col.data_type LIKE '%FLOAT%' THEN -- For numeric columns, use NVL with a default numeric value v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", -1) = NVL(B."' || col.column_name || '", -1)'; ELSIF col.data_type = 'DATE' THEN -- For date columns, use NVL and comparing NULL dates v_where_clause := v_where_clause || ' AND NVL(TO_CHAR(A."' || col.column_name || '", ''YYYYMMDD''), '''') = NVL(TO_CHAR(B."' || col.column_name || '", ''YYYYMMDD''), '''')'; ELSIF col.data_type LIKE '%TIMESTAMP%' THEN -- For timestamp columns, convert to a comparable format, e.g., down to seconds v_where_clause := v_where_clause || ' AND NVL(TO_CHAR(A."' || col.column_name || '", ''YYYYMMDDHH24MISS''), '''') = NVL(TO_CHAR(B."' || col.column_name || '", ''YYYYMMDDHH24MISS''), '''')'; ELSE -- Default fallback: treat as a VARCHAR2 (for data types like RAW, etc.) v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", '''') = NVL(B."' || col.column_name || '", '''')'; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('Unique Columns: ' || p_unique_columns); DBMS_OUTPUT.PUT_LINE('WHERE Clause: ' || v_where_clause); -- Example query using the dynamically built WHERE clause EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM DGSTREAM.LK_CREDIT_AGREEMENT A WHERE ROWNUM IN ( SELECT ROWNUM FROM ( SELECT ROWNUM, ROW_NUMBER() OVER (PARTITION BY ' || p_unique_columns || ' ORDER BY ROWNUM) rn FROM DGSTREAM.LK_CREDIT_AGREEMENT) WHERE rn > 1) WHERE ' || v_where_clause; END;/

INLINE V2

DECLARE    p_table_name VARCHAR2(255);    p_owner VARCHAR2(255) := 'DGSTREAM'; -- Replace with your schema    v_sql_check VARCHAR2(32767);    v_sql_columns VARCHAR2(32767);    v_dup_count NUMBER;    v_where_clause VARCHAR2(32767) := '1=1'; -- Initialize the WHERE clause with '1=1'    CURSOR table_cur IS        SELECT table_name        FROM all_tables        WHERE owner = UPPER(p_owner)        AND table_name LIKE 'LX_%'        AND table_name NOT LIKE '%$%'        AND table_name NOT LIKE '%AUDIT%'        AND table_name NOT LIKE '%_DUPS' -- Exclude already processed or backup tables        AND table_name NOT LIKE '%_HIST' -- Exclude history tables        AND table_name NOT IN (select distinct table_name from sys.all_tab_cols where data_type in ('CLOB', 'BLOB'))        ORDER BY 1;BEGIN    -- Loop through each table in the schema    FOR t IN table_cur LOOP        p_table_name := t.table_name;                -- Generate the column list dynamically, excluding UUID and PROCESSED_TS        v_where_clause := '1=1'; -- Reset the WHERE clause for each table        FOR col IN (SELECT column_name, data_type                    FROM all_tab_columns                    WHERE table_name = UPPER(p_table_name)                    AND owner = UPPER(p_owner)                    AND column_name NOT IN ('UUID', 'PROCESSED_TS')                    ORDER BY column_id) LOOP            -- Dynamically add each column comparison to the WHERE clause based on data type            IF col.data_type LIKE '%CHAR%' OR col.data_type = 'VARCHAR2' OR col.data_type = 'NCHAR' THEN                -- For character columns, use NVL with an empty string                v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", '''') = NVL(B."' || col.column_name || '", '''')';            ELSIF col.data_type = 'NUMBER' OR col.data_type LIKE 'FLOAT%' THEN                -- For numeric columns, use NVL with a default numeric value                v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", -1) = NVL(B."' || col.column_name || '", -1)';            ELSIF col.data_type = 'DATE' THEN                -- For date columns, use NVL and convert to avoid comparing NULL dates                v_where_clause := v_where_clause || ' AND NVL(TO_CHAR(A."' || col.column_name || '", ''YYYYMMDD''), '''') = NVL(TO_CHAR(B."' || col.column_name || '", ''YYYYMMDD''), '''')';            ELSE                -- Default fallback: treat as a VARCHAR2 (for other data types like RAW, etc.)                v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", '''') = NVL(B."' || col.column_name || '", '''')';            END IF;        END LOOP;
        -- Output the dynamically generated WHERE clause for debugging        DBMS_OUTPUT.PUT_LINE('WHERE clause for table ' || p_table_name || ': ' || v_where_clause);
        -- Prepare SQL for duplicate checking using dynamic columns        v_sql_check := 'SELECT COUNT(*) FROM ' || p_owner || '.' || p_table_name || ' A ' ||                       ' WHERE ROWID > (SELECT MIN(ROWID) FROM ' || p_owner || '.' || p_table_name || ' B ' ||                       ' WHERE ' || v_where_clause || ')';                -- Debug the SQL        DBMS_OUTPUT.PUT_LINE('Generated SQL: ' || v_sql_check);                -- Execute the duplicate check        EXECUTE IMMEDIATE v_sql_check INTO v_dup_count;                -- Output the duplicate count        DBMS_OUTPUT.PUT_LINE('Duplicate count for table ' || p_table_name || ': ' || v_dup_count);    END LOOP;EXCEPTION    WHEN OTHERS THEN        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);END;/

InLine VErsion

DECLARE    p_table_name VARCHAR2(255);    p_owner VARCHAR2(255) := 'YOUR_SCHEMA'; -- Replace with your schema    v_sql_check VARCHAR2(32767);    v_sql_columns VARCHAR2(32767);    v_dup_count NUMBER;    v_where_clause VARCHAR2(32767) := '1=1'; -- Initialize the WHERE clause with '1=1'    CURSOR table_cur IS        SELECT table_name        FROM all_tables        WHERE owner = UPPER(p_owner)        AND table_name NOT LIKE '%_DUPS'; -- Exclude already processed or backup tablesBEGIN    -- Loop through each table in the schema    FOR t IN table_cur LOOP        p_table_name := t.table_name;                -- Generate the column list dynamically, excluding UUID and PROCESSED_TS        v_where_clause := '1=1'; -- Reset the WHERE clause for each table        FOR col IN (SELECT column_name                    FROM all_tab_columns                    WHERE table_name = UPPER(p_table_name)                    AND owner = UPPER(p_owner)                    AND column_name NOT IN ('UUID', 'PROCESSED_TS')                    ORDER BY column_id) LOOP            -- Dynamically add each column comparison to the WHERE clause            v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", '''') = NVL(B."' || col.column_name || '", '''')';        END LOOP;
        -- Output the dynamically generated WHERE clause for debugging        DBMS_OUTPUT.PUT_LINE('WHERE clause for table ' || p_table_name || ': ' || v_where_clause);
        -- Prepare SQL for duplicate checking using dynamic columns        v_sql_check := 'SELECT COUNT(*) FROM ' || p_owner || '.' || p_table_name || ' A ' ||                       ' WHERE ROWID > (SELECT MIN(ROWID) FROM ' || p_owner || '.' || p_table_name || ' B ' ||                       ' WHERE ' || v_where_clause || ')';                -- Debug the SQL        DBMS_OUTPUT.PUT_LINE('Generated SQL: ' || v_sql_check);                -- Execute the duplicate check        EXECUTE IMMEDIATE v_sql_check INTO v_dup_count;                -- Output the duplicate count        DBMS_OUTPUT.PUT_LINE('Duplicate count for table ' || p_table_name || ': ' || v_dup_count);    END LOOP;EXCEPTION    WHEN OTHERS THEN        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);END;/

V2

CREATE OR REPLACE PACKAGE DUPLICATE_CLEANUP_PKG AUTHID CURRENT_USER AS    PROCEDURE CLEANUP(        p_table_name IN VARCHAR2, -- Table name to clean        p_owner IN VARCHAR2, -- Schema/owner of the table        p_unique_columns IN VARCHAR2, -- Comma-separated list of columns for identifying duplicates        p_confirm_delete IN VARCHAR2 -- 'Y' to confirm deletion, 'N' to skip deletion    );        PROCEDURE RESTORE_DUPS(        p_table_name IN VARCHAR2, -- Table to restore        p_owner IN VARCHAR2 -- Schema/owner of the table    );        PROCEDURE CHECK_4DUPS(        p_table_name IN VARCHAR2, -- Table to check for duplicates        p_owner IN VARCHAR2, -- Schema/owner of the table        p_unique_columns IN VARCHAR2 -- Comma-separated list of unique columns to identify duplicates    );END DUPLICATE_CLEANUP_PKG;/
CREATE OR REPLACE PACKAGE BODY DUPLICATE_CLEANUP_PKG AS    PROCEDURE CLEANUP(        p_table_name IN VARCHAR2,        p_owner IN VARCHAR2,        p_unique_columns IN VARCHAR2, -- comma-separated list of unique columns to identify duplicates        p_confirm_delete IN VARCHAR2 -- 'Y' to confirm deletion, 'N' to skip deletion    ) IS        v_sql VARCHAR2(4000);        v_delete_sql VARCHAR2(4000);        v_backup_sql VARCHAR2(4000);        v_create_backup VARCHAR2(4000);        v_table_dups VARCHAR2(255);        v_where_clause VARCHAR2(4000);        v_count NUMBER;        v_dup_count NUMBER;        v_column_type VARCHAR2(30);        v_dups_count NUMBER; -- To check if rows exist in the _DUPS table    BEGIN        -- Enable DBMS_OUTPUT        DBMS_OUTPUT.ENABLE;
        -- Initialize the WHERE clause with '1=1'        v_where_clause := '1=1';        DBMS_OUTPUT.PUT_LINE('Building WHERE clause for unique columns...'); -- Debug statement                -- Dynamically build the WHERE clause based on unique columns, handling NULLs with NVL        FOR col IN (SELECT trim(regexp_substr(p_unique_columns, '[^,]+', 1, LEVEL)) AS column_name                    FROM dual                    CONNECT BY regexp_substr(p_unique_columns, '[^,]+', 1, LEVEL) IS NOT NULL) LOOP            -- Fetch the column data type from USER_TAB_COLUMNS            SELECT data_type INTO v_column_type            FROM all_tab_columns            WHERE table_name = UPPER(p_table_name)              AND column_name = UPPER(col.column_name)              AND owner = UPPER(p_owner);
            DBMS_OUTPUT.PUT_LINE('Processing column: ' || col.column_name || ' with data type: ' || v_column_type); -- Debug statement
            -- For character columns, use NVL with an empty string, and for numeric columns, use NVL with a numeric default            IF v_column_type LIKE '%CHAR%' OR v_column_type = 'VARCHAR2' OR v_column_type = 'NCHAR' THEN                v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", '''') = NVL(B."' || col.column_name || '", '''')';            ELSIF v_column_type = 'NUMBER' OR v_column_type LIKE 'FLOAT%' THEN                v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", -1) = NVL(B."' || col.column_name || '", -1)';            ELSE                -- If it's neither a character nor numeric, handle it as VARCHAR2 (just a fallback)                v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", '''') = NVL(B."' || col.column_name || '", '''')';            END IF;
            DBMS_OUTPUT.PUT_LINE('Current WHERE clause: ' || v_where_clause); -- Debug statement        END LOOP;
        -- Print final WHERE clause        DBMS_OUTPUT.PUT_LINE('Final WHERE clause: ' || v_where_clause); -- Debug statement
        -- Check for duplicates based on unique columns        v_sql := 'SELECT COUNT(*) FROM ' || p_owner || '.' || p_table_name || ' A ' ||                 ' WHERE ROWID > (SELECT MIN(ROWID) FROM ' || p_owner || '.' || p_table_name || ' B ' ||                 ' WHERE ' || v_where_clause || ')';                -- Debug the SELECT query        DBMS_OUTPUT.PUT_LINE('Executing duplicate count SQL: ' || v_sql); -- Debug statement
        -- Execute the duplicate check        EXECUTE IMMEDIATE v_sql INTO v_dup_count;        DBMS_OUTPUT.PUT_LINE('Duplicate count: ' || v_dup_count); -- Debug statement
        -- If no duplicates are found, exit the procedure        IF v_dup_count = 0 THEN            DBMS_OUTPUT.PUT_LINE('No duplicates found. Exiting procedure.');            RETURN;        END IF;
        -- Only if duplicates are found, proceed to create the _DUPS table if it doesn't exist        v_table_dups := p_owner || '.' || p_table_name || '_DUPS';
        -- Check if the _DUPS table already exists        SELECT COUNT(*)        INTO v_count        FROM all_tables        WHERE table_name = UPPER(p_table_name || '_DUPS')        AND owner = UPPER(p_owner);
        -- If the _DUPS table does not exist, create it        IF v_count = 0 THEN            v_create_backup := 'CREATE TABLE ' || v_table_dups || ' AS SELECT * FROM ' || p_owner || '.' || p_table_name || ' WHERE 1=0';            DBMS_OUTPUT.PUT_LINE('Creating _DUPS table: ' || v_create_backup); -- Debug statement            EXECUTE IMMEDIATE v_create_backup;            DBMS_OUTPUT.PUT_LINE('_DUPS table ' || v_table_dups || ' created.');        ELSE            DBMS_OUTPUT.PUT_LINE('_DUPS table ' || v_table_dups || ' already exists.');        END IF;
        -- Check if rows already exist in the _DUPS table        v_sql := 'SELECT COUNT(*) FROM ' || v_table_dups;        EXECUTE IMMEDIATE v_sql INTO v_dups_count;                IF v_dups_count > 0 THEN            DBMS_OUTPUT.PUT_LINE('Rows already exist in the _DUPS table. Skipping backup.');        ELSE            -- Backup duplicate records before deletion            v_backup_sql := 'INSERT INTO ' || v_table_dups || ' SELECT * FROM ' || p_owner || '.' || p_table_name || ' A ' ||                            ' WHERE ROWID IN ( ' ||                            ' SELECT ROWID FROM ( ' ||                            ' SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY ' || p_unique_columns || ' ORDER BY ROWID) rn ' ||                            ' FROM ' || p_owner || '.' || p_table_name || ') ' ||                            ' WHERE rn > 1)';
            -- Debug the backup SQL            DBMS_OUTPUT.PUT_LINE('Executing backup SQL: ' || v_backup_sql); -- Debug statement
            -- Execute the backup            EXECUTE IMMEDIATE v_backup_sql;            DBMS_OUTPUT.PUT_LINE('Duplicate records backed up in ' || v_table_dups);        END IF;
        -- Check if the user confirmed deletion        IF UPPER(p_confirm_delete) = 'Y' THEN            -- Now delete duplicates, leaving one row for each duplicate group            v_delete_sql := 'DELETE FROM ' || p_owner || '.' || p_table_name || ' A ' ||                            ' WHERE ROWID IN ( ' ||                            ' SELECT ROWID FROM ( ' ||                            ' SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY ' || p_unique_columns || ' ORDER BY ROWID) rn ' ||                            ' FROM ' || p_owner || '.' || p_table_name || ') ' ||                            ' WHERE rn > 1)';
            -- Debug the delete SQL            DBMS_OUTPUT.PUT_LINE('Executing delete SQL: ' || v_delete_sql); -- Debug statement
            -- Execute the delete statement            EXECUTE IMMEDIATE v_delete_sql;            DBMS_OUTPUT.PUT_LINE('Duplicates deleted from ' || p_owner || '.' || p_table_name);        ELSE            DBMS_OUTPUT.PUT_LINE('Deletion skipped as confirmation not received.');        END IF;
    -- Exception block for error handling    EXCEPTION        WHEN OTHERS THEN            DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM); -- Debug statement for error    END CLEANUP;
    -- Restore data from the _DUPS table    PROCEDURE RESTORE_DUPS(        p_table_name IN VARCHAR2,        p_owner IN VARCHAR2    ) IS        v_sql_restore VARCHAR2(4000);        v_columns VARCHAR2(4000);        v_column_list VARCHAR2(4000);        v_table_dups VARCHAR2(255);    BEGIN        -- Enable DBMS_OUTPUT        DBMS_OUTPUT.ENABLE;
        -- Define the name of the _DUPS table        v_table_dups := p_owner || '.' || p_table_name || '_DUPS';
        -- Retrieve column list excluding UUID (or autogenerated column)        SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id)        INTO v_column_list        FROM all_tab_columns        WHERE table_name = UPPER(p_table_name)        AND owner = UPPER(p_owner)        AND column_name NOT IN ('UUID'); -- Assuming 'UUID' is the name of the autogenerated column
        -- Prepare the restore SQL, excluding the UUID column        v_sql_restore := 'INSERT INTO ' || p_owner || '.' || p_table_name ||                         ' (' || v_column_list || ') ' ||                         ' SELECT ' || v_column_list || ' FROM ' || v_table_dups;
        -- Debug the restore SQL        DBMS_OUTPUT.PUT_LINE('Executing restore SQL: ' || v_sql_restore); -- Debug statement
        -- Execute the restore SQL        EXECUTE IMMEDIATE v_sql_restore;        DBMS_OUTPUT.PUT_LINE('Data restored from ' || v_table_dups || ' to ' || p_table_name);    EXCEPTION        WHEN OTHERS THEN            DBMS_OUTPUT.PUT_LINE('Error during restore: ' || SQLERRM); -- Error handling    END RESTORE_DUPS;
    -- Procedure to check for duplicates without creating tables or deleting rows    PROCEDURE CHECK_4DUPS(        p_table_name IN VARCHAR2, -- Table to check for duplicates        p_owner IN VARCHAR2, -- Schema/owner of the table        p_unique_columns IN VARCHAR2 -- Comma-separated list of unique columns to identify duplicates    ) IS        v_sql VARCHAR2(4000);        v_where_clause VARCHAR2(4000);        v_dup_count NUMBER;        v_column_type VARCHAR2(30);    BEGIN        -- Enable DBMS_OUTPUT        DBMS_OUTPUT.ENABLE;
        -- Initialize the WHERE clause with '1=1'        v_where_clause := '1=1';        DBMS_OUTPUT.PUT_LINE('Building WHERE clause for unique columns...'); -- Debug statement                -- Dynamically build the WHERE clause based on unique columns, handling NULLs with NVL        FOR col IN (SELECT trim(regexp_substr(p_unique_columns, '[^,]+', 1, LEVEL)) AS column_name                    FROM dual                    CONNECT BY regexp_substr(p_unique_columns, '[^,]+', 1, LEVEL) IS NOT NULL) LOOP            -- Fetch the column data type from USER_TAB_COLUMNS            SELECT data_type INTO v_column_type            FROM all_tab_columns            WHERE table_name = UPPER(p_table_name)              AND column_name = UPPER(col.column_name)              AND owner = UPPER(p_owner);
            DBMS_OUTPUT.PUT_LINE('Processing column: ' || col.column_name || ' with data type: ' || v_column_type); -- Debug statement
            -- For character columns, use NVL with an empty string, and for numeric columns, use NVL with a numeric default            IF v_column_type LIKE '%CHAR%' OR v_column_type = 'VARCHAR2' OR v_column_type = 'NCHAR' THEN                v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", '''') = NVL(B."' || col.column_name || '", '''')';            ELSIF v_column_type = 'NUMBER' OR v_column_type LIKE 'FLOAT%' THEN                v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", -1) = NVL(B."' || col.column_name || '", -1)';            ELSE                -- If it's neither a character nor numeric, handle it as VARCHAR2 (just a fallback)                v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", '''') = NVL(B."' || col.column_name || '", '''')';            END IF;
            DBMS_OUTPUT.PUT_LINE('Current WHERE clause: ' || v_where_clause); -- Debug statement        END LOOP;
        -- Check for duplicates based on unique columns        v_sql := 'SELECT COUNT(*) FROM ' || p_owner || '.' || p_table_name || ' A ' ||                 ' WHERE ROWID > (SELECT MIN(ROWID) FROM ' || p_owner || '.' || p_table_name || ' B ' ||                 ' WHERE ' || v_where_clause || ')';
        -- Execute the duplicate check        EXECUTE IMMEDIATE v_sql INTO v_dup_count;        DBMS_OUTPUT.PUT_LINE('Duplicate count: ' || v_dup_count);    END CHECK_4DUPS;
END DUPLICATE_CLEANUP_PKG;/
-- Run the cleanup procedureBEGIN    DUPLICATE_CLEANUP_PKG.CLEANUP('MY_TABLE', 'MY_SCHEMA', 'COLUMN1, COLUMN2', 'Y');END;/
-- Run the restore procedureBEGIN    DUPLICATE_CLEANUP_PKG.RESTORE_DUPS('MY_TABLE', 'MY_SCHEMA');END;/
-- Run the check for duplicates onlyBEGIN    DUPLICATE_CLEANUP_PKG.CHECK_4DUPS('MY_TABLE', 'MY_SCHEMA', 'COLUMN1, COLUMN2');END;/

V1

--#### **Permissions Section**-- Grant necessary privileges to DGSTREAM schemaGRANT CREATE TABLE TO DGSTREAM;GRANT INSERT, DELETE, EXECUTE ON SCHEMA_NAME.* TO DGSTREAM;-- Ensure sufficient quota on the tablespaceALTER USER DGSTREAM QUOTA UNLIMITED ON USERS;
--#### **Initialization Section: Logging Table Creation**
-- Create the logging table if it doesn't existBEGIN    EXECUTE IMMEDIATE '        CREATE TABLE DUPLICATE_CLEANUP_LOG (            log_id NUMBER GENERATED BY DEFAULT AS IDENTITY,            table_name VARCHAR2(255),            owner VARCHAR2(255),            error_msg VARCHAR2(4000),            log_date DATE DEFAULT SYSDATE,            PRIMARY KEY (log_id)        )';EXCEPTION    WHEN OTHERS THEN        IF SQLCODE = -955 THEN            -- Table already exists, ignore the error            DBMS_OUTPUT.PUT_LINE('DUPLICATE_CLEANUP_LOG table already exists.');        ELSE            -- Reraise the exception for other errors            RAISE;        END IF;END;/--#### **Package Header**CREATE OR REPLACE PACKAGE DUPLICATE_CLEANUP_PKG AUTHID CURRENT_USER AS    PROCEDURE CLEANUP(        p_table_name IN VARCHAR2, -- Table name to clean        p_owner IN VARCHAR2, -- Schema/owner of the table        p_unique_columns IN VARCHAR2, -- Comma-separated list of columns for identifying duplicates        p_confirm_delete IN VARCHAR2 -- 'Y' to confirm deletion, 'N' to skip deletion    );        PROCEDURE RESTORE_BACKUP(        p_table_name IN VARCHAR2, -- Table to restore        p_owner IN VARCHAR2 -- Schema/owner of the table    );END DUPLICATE_CLEANUP_PKG;/
--#### **Package Body**-- Create the package bodyCREATE OR REPLACE PACKAGE BODY DUPLICATE_CLEANUP_PKG AS    PROCEDURE CLEANUP(        p_table_name IN VARCHAR2,        p_owner IN VARCHAR2,        p_unique_columns IN VARCHAR2, -- comma-separated list of unique columns to identify duplicates        p_confirm_delete IN VARCHAR2 -- 'Y' to confirm deletion, 'N' to skip deletion    ) IS        v_sql VARCHAR2(4000);        v_delete_sql VARCHAR2(4000);        v_backup_sql VARCHAR2(4000);        v_create_backup VARCHAR2(4000);        v_table_backup VARCHAR2(255);        v_where_clause VARCHAR2(4000);        v_count NUMBER;        v_dup_count NUMBER;        v_column_type VARCHAR2(30);        v_backup_count NUMBER; -- To check if rows exist in the backup table    BEGIN        -- Enable DBMS_OUTPUT        DBMS_OUTPUT.ENABLE;
        -- Initialize the WHERE clause with '1=1'        v_where_clause := '1=1';        DBMS_OUTPUT.PUT_LINE('Building WHERE clause for unique columns...'); -- Debug statement                -- Dynamically build the WHERE clause based on unique columns, handling NULLs with NVL        FOR col IN (SELECT trim(regexp_substr(p_unique_columns, '[^,]+', 1, LEVEL)) AS column_name                    FROM dual                    CONNECT BY regexp_substr(p_unique_columns, '[^,]+', 1, LEVEL) IS NOT NULL) LOOP            -- Fetch the column data type from USER_TAB_COLUMNS            SELECT data_type INTO v_column_type            FROM all_tab_columns            WHERE table_name = UPPER(p_table_name)              AND column_name = UPPER(col.column_name)              AND owner = UPPER(p_owner);
            DBMS_OUTPUT.PUT_LINE('Processing column: ' || col.column_name || ' with data type: ' || v_column_type); -- Debug statement
            -- For character columns, use NVL with an empty string, and for numeric columns, use NVL with a numeric default            IF v_column_type LIKE '%CHAR%' OR v_column_type = 'VARCHAR2' OR v_column_type = 'NCHAR' THEN                v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", '''') = NVL(B."' || col.column_name || '", '''')';            ELSIF v_column_type = 'NUMBER' OR v_column_type LIKE 'FLOAT%' THEN                v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", -1) = NVL(B."' || col.column_name || '", -1)';            ELSE                -- If it's neither a character nor numeric, handle it as VARCHAR2 (just a fallback)                v_where_clause := v_where_clause || ' AND NVL(A."' || col.column_name || '", '''') = NVL(B."' || col.column_name || '", '''')';            END IF;
            DBMS_OUTPUT.PUT_LINE('Current WHERE clause: ' || v_where_clause); -- Debug statement        END LOOP;
        -- Print final WHERE clause        DBMS_OUTPUT.PUT_LINE('Final WHERE clause: ' || v_where_clause); -- Debug statement
        -- Check for duplicates based on unique columns        v_sql := 'SELECT COUNT(*) FROM ' || p_owner || '.' || p_table_name || ' A ' ||                 ' WHERE ROWID > (SELECT MIN(ROWID) FROM ' || p_owner || '.' || p_table_name || ' B ' ||                 ' WHERE ' || v_where_clause || ')';                -- Debug the SELECT query        DBMS_OUTPUT.PUT_LINE('Executing duplicate count SQL: ' || v_sql); -- Debug statement
        -- Execute the duplicate check        EXECUTE IMMEDIATE v_sql INTO v_dup_count;        DBMS_OUTPUT.PUT_LINE('Duplicate count: ' || v_dup_count); -- Debug statement
        -- If no duplicates are found, exit the procedure        IF v_dup_count = 0 THEN            DBMS_OUTPUT.PUT_LINE('No duplicates found. Exiting procedure.');            RETURN;        END IF;
        -- Only if duplicates are found, proceed to create the backup table if it doesn't exist        v_table_backup := p_owner || '.' || p_table_name || '_BACKUP';
        -- Check if the backup table already exists        SELECT COUNT(*)        INTO v_count        FROM all_tables        WHERE table_name = UPPER(p_table_name || '_BACKUP')        AND owner = UPPER(p_owner);
        -- If the backup table does not exist, create it        IF v_count = 0 THEN            v_create_backup := 'CREATE TABLE ' || v_table_backup || ' AS SELECT * FROM ' || p_owner || '.' || p_table_name || ' WHERE 1=0';            DBMS_OUTPUT.PUT_LINE('Creating backup table: ' || v_create_backup); -- Debug statement            EXECUTE IMMEDIATE v_create_backup;            DBMS_OUTPUT.PUT_LINE('Backup table ' || v_table_backup || ' created.');        ELSE            DBMS_OUTPUT.PUT_LINE('Backup table ' || v_table_backup || ' already exists.');        END IF;
        -- Check if rows already exist in the backup table        v_sql := 'SELECT COUNT(*) FROM ' || v_table_backup;        EXECUTE IMMEDIATE v_sql INTO v_backup_count;                IF v_backup_count > 0 THEN            DBMS_OUTPUT.PUT_LINE('Rows already exist in the backup table. Skipping backup.');        ELSE            -- Backup duplicate records before deletion            v_backup_sql := 'INSERT INTO ' || v_table_backup || ' SELECT * FROM ' || p_owner || '.' || p_table_name || ' A ' ||                            ' WHERE ROWID IN ( ' ||                            ' SELECT ROWID FROM ( ' ||                            ' SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY ' || p_unique_columns || ' ORDER BY ROWID) rn ' ||                            ' FROM ' || p_owner || '.' || p_table_name || ') ' ||                            ' WHERE rn > 1)';
            -- Debug the backup SQL            DBMS_OUTPUT.PUT_LINE('Executing backup SQL: ' || v_backup_sql); -- Debug statement
            -- Execute the backup            EXECUTE IMMEDIATE v_backup_sql;            DBMS_OUTPUT.PUT_LINE('Duplicate records backed up in ' || v_table_backup);        END IF;
        -- Check if the user confirmed deletion        IF UPPER(p_confirm_delete) = 'Y' THEN            -- Now delete duplicates, leaving one row for each duplicate group            v_delete_sql := 'DELETE FROM ' || p_owner || '.' || p_table_name || ' A ' ||                            ' WHERE ROWID IN ( ' ||                            ' SELECT ROWID FROM ( ' ||                            ' SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY ' || p_unique_columns || ' ORDER BY ROWID) rn ' ||                            ' FROM ' || p_owner || '.' || p_table_name || ') ' ||                            ' WHERE rn > 1)';
            -- Debug the delete SQL            DBMS_OUTPUT.PUT_LINE('Executing delete SQL: ' || v_delete_sql); -- Debug statement
            -- Execute the delete statement            EXECUTE IMMEDIATE v_delete_sql;            DBMS_OUTPUT.PUT_LINE('Duplicates deleted from ' || p_owner || '.' || p_table_name);        ELSE            DBMS_OUTPUT.PUT_LINE('Deletion skipped as confirmation not received.');        END IF;
    -- Exception block for error handling    EXCEPTION        WHEN OTHERS THEN            DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM); -- Debug statement for error    END CLEANUP;
    -- Restore data from the _BACKUP table    PROCEDURE RESTORE_BACKUP(        p_table_name IN VARCHAR2,        p_owner IN VARCHAR2    ) IS        v_sql_restore VARCHAR2(4000);        v_columns VARCHAR2(4000);        v_column_list VARCHAR2(4000);        v_table_backup VARCHAR2(255);    BEGIN        -- Enable DBMS_OUTPUT        DBMS_OUTPUT.ENABLE;
        -- Define the name of the backup table        v_table_backup := p_owner || '.' || p_table_name || '_BACKUP';
        -- Retrieve column list excluding UUID (or autogenerated column)        SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id)        INTO v_column_list        FROM all_tab_columns        WHERE table_name = UPPER(p_table_name)        AND owner = UPPER(p_owner)        AND column_name NOT IN ('UUID'); -- Assuming 'UUID' is the name of the autogenerated column
        -- Prepare the restore SQL, excluding the UUID column        v_sql_restore := 'INSERT INTO ' || p_owner || '.' || p_table_name ||                         ' (' || v_column_list || ') ' ||                         ' SELECT ' || v_column_list || ' FROM ' || v_table_backup;
        -- Debug the restore SQL        DBMS_OUTPUT.PUT_LINE('Executing restore SQL: ' || v_sql_restore); -- Debug statement
        -- Execute the restore SQL        EXECUTE IMMEDIATE v_sql_restore;        DBMS_OUTPUT.PUT_LINE('Data restored from ' || v_table_backup || ' to ' || p_table_name);    EXCEPTION        WHEN OTHERS THEN            DBMS_OUTPUT.PUT_LINE('Error during restore: ' || SQLERRM); -- Error handling    END RESTORE_BACKUP;END DUPLICATE_CLEANUP_PKG;/

--#### **Execution**-- Example execution--BEGIN-- DUPLICATE_CLEANUP_PKG.CLEANUP('MY_TABLE', 'MY_SCHEMA', 'COLUMN1, COLUMN2', 'Y');--END;/
--Rollback--BEGIN -- DUPLICATE_CLEANUP_PKG.RESTORE_BACKUP('MY_TABLE', 'MY_SCHEMA'); --END;