Create index (in oracle DB), only if it does not exist

Viewed 650

I would like to create the following index

CREATE INDEX timevariable_idx_varvalue_projectid 
    ON timevariable (varvalue,projectid);

only if does not exist, but I'm having a hard time doing it would anyone know how to do so?!

2 Answers

Unfortunately Oracle does not support the IF NOT EXISTS clause to CREATE statements (I don't know about APEX though, which you also tagged your question with).

So you need execute immediate in a code block. This Ask TOM article gives an elegant solution that works by trapping exceptions.

Adapted to your use case, this would be:

set serveroutput on
declare 
    already_exists  exception; 
    columns_indexed exception;
    pragma exception_init(already_exists, -955); 
    pragma exception_init(columns_indexed, -1408);
begin 
    execute immediate 'create index timevariable_idx_varvalue_projectid ON timevariable (varvalue,projectid)'; 
    dbms_output.put_line('created'); 
exception 
    when already_exists or columns_indexed then 
        dbms_output.put_line('skipped');  
end;  

Oracle has no "IF NOT EXISTS" syntax in its DDL commands. If you execute the CREATE command, you either need to accept the error in your script as ok to ignore, or handle the error in some way. If you want to avoid the execute commend unless necessary, you'd need to check the data dictionary for the index first, then execute if required:

declare
   l_count number;
begin
   select count(*) into l_count from dba_indexes where index_name='TIMEVARIABLE_IDX_VARVALUE_PROJECTID';
   if l_count = 0 then
      execute immediate 'CREATE INDEX timevariable_idx_varvalue_projectid ON timevariable (varvalue,projectid)';
   end if;
end;
Related