More bad code


Someone had coded up a list of explicitly 'alter index ... unusable' command, plus a 'drop index' command for the unique indexes. I mentioned that it would be good to process these within a PL/SQL loop, so that if the index names changed, or more indexes were added between the time this code was written and run against the target database, then it would handle those changes

This was the result...

         FOR i IN 1..18
         LOOP
           SELECT DECODE(i,'1','XYZ_DOB_I','2','XYZ_DRIVING_LICENCE_NO_UP_FNI','3','XYZ_FORENAME1_FNI',
                           '4','XYZ_GUS_FK_I','5','XYZ_ID_SID_NO_I','6','XYZ_MAID_FOR_UP_FNI',
                           '7','XYZ_OFF1_FK_I','8','XYZ_RC_ETHNIC_FK_I','9','XYZ_RC_TITLE_FK_I',
                           '10','XYZ_SASS_FK_I','11','XYZ_SID_NUMBER_UP_FNI','12','XYZ_SNDSURNAME_FNI',
                           '13','XYZ_SUR_FOR_UP_FNI','14','XYZ_UP_FORENAME1_FNI','15','XYZ_PK',
                           '16','XYZ_DOCKET_NO_FNI','17','XYZ_OLD_DOCKET_NO_FNI','18','XYZ_FNI_NO_FNI')
           INTO lc_index_name
           FROM DUAL;

           IF i < 16 THEN
             BEGIN
               IF i < 15 THEN
                 EXECUTE IMMEDIATE 'ALTER INDEX '||lc_index_name||' UNUSABLE';
               END IF;
             EXCEPTION
               WHEN OTHERS THEN
                 IF SQLCODE = -1418 THEN
                   NULL;
                 ELSE
                   RAISE;
                 END IF;
             END;
           ELSE
             BEGIN
               EXECUTE IMMEDIATE 'DROP INDEX '||lc_index_name;
             EXCEPTION
               WHEN OTHERS THEN
                 IF SQLCODE = -1418 THEN
                   NULL;
                 ELSE
                   RAISE;
                 END IF;
             END;
           END IF;
         END LOOP;
         

Someone please shoot me...