PL/SQL dynamic Index loop based on list variable

Question

Currently, I am indexing column by column with the following code snippet:

DECLARE
 already_exists EXCEPTION;
 columns_indexed EXCEPTION;
 PRAGMA EXCEPTION_INIT ( already_exists, -955 );
 PRAGMA EXCEPTION_INIT (columns_indexed, -1408);
BEGIN
 EXECUTE IMMEDIATE 'Create Index TABLE_A_COLUMN_A on TABLE_A(COLUMN_A)';
EXCEPTION
 WHEN already_exists or columns_indexed
 THEN
 NULL;
END;

Is it possible to replace COLUMN_A in this code

a) with a list reference and iterate through it dynamically?

declare @myList varchar(100)
set @myList = 'COLUMN_A,COLUMN_B,COLUMN_C'

b) and make sure the loop keeps looping even when it encounters an issue with one column? In other words, not the whole loop should fail if there is an issue with one of the columns

Answer 1

Yes, of course, simply use a collection:

declare
 type list_tab_type is table of varchar2(128);
 list_tab list_tab_type := list_tab_type('column_a','column_b','column_c');
begin
 for i in list_tab.first .. list_tab.last
 loop
 begin
 EXECUTE IMMEDIATE 'Create Index TABLE_A_'||list_tab(i)||' on TABLE_A('||list_tab(i)||')';
 exception
 when others then
 dbms_output.put_line(list_tab(i)||': '||SQLERRM);
 end;
 end loop;
end;

The when others traps all errors, preventing the loop from breaking if one of the DDLs fails.

However, creating indexes like this using PL/SQL is not typical. Normally indexes are created with manual DDLs one-time and not with code. You may want to consider whether you are using the right approach for your larger task.