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?!
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?!
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;