indexes

DBMS_OUTPUT.PUT_LINE('-- ===== ALL INDEXES =====');

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
    WHERE ui.table_name = 'ZORDERDETAIL'
    GROUP BY ui.index_name, ui.uniqueness
) LOOP
    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;