index2

SET SERVEROUTPUT ON; -- For SQL*Plus / Script Output
DECLARE
    v_sql VARCHAR2(4000);
    v_found BOOLEAN := FALSE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('-- Check: Script is running...');

    FOR rec IN (
        SELECT ui.index_name,
               ui.uniqueness,
               LISTAGG(uic.column_name, ', ') WITHIN GROUP (ORDER BY uic.column_position) AS cols
        FROM user_indexes ui
        JOIN user_ind_columns uic ON ui.index_name = uic.index_name
        -- Ensure table name is exactly as stored in the data dictionary (usually UPPERCASE)
        WHERE ui.table_name = 'ZORDERDETAIL' 
        GROUP BY ui.index_name, ui.uniqueness
    ) LOOP
        v_found := TRUE;
        IF rec.uniqueness = 'UNIQUE' THEN
            v_sql := 'CREATE UNIQUE INDEX ' || rec.index_name || '_NEW ON ZOrderTable_new (' || rec.cols || ');';
        ELSE
            v_sql := 'CREATE INDEX ' || rec.index_name || '_NEW ON ZOrderTable_new (' || rec.cols || ');';
        END IF;
        DBMS_OUTPUT.PUT_LINE(v_sql);
    END LOOP;

    IF NOT v_found THEN
        DBMS_OUTPUT.PUT_LINE('-- Warning: No indexes found for table ZORDERDETAIL. Check case sensitivity!');
    END IF;
END;
/