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

Paul W · Accepted Answer · 2024-04-22 01:13:32Z

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.

Stack Exchange Network

PL/SQL dynamic Index loop based on list variable

1 Answer 1

Hot Network Questions

PL/SQL dynamic Index loop based on list variable

1 Answer 1

Related

Hot Network Questions