How to create an index on CASE expression in Oracle

All we need is an easy explanation of the problem, so here it is.

I am trying to create an index on an oracle table as it follows

DECLARE MGMT_ID NUMBER;
        SSHKEY_MGMT_ID NUMBER;

BEGIN
    SELECT ID INTO MGMT_ID FROM POLICY_SETS WHERE POLICY_SET_NAME = 'MGMT';
    SELECT ID INTO SSHKEY_MGMT_ID FROM POLICY_SETS WHERE POLICY_SET_NAME = 'SSHKEY_MGMT';

    CREATE UNIQUE INDEX PASSWORD_OBJECT_UI_1
        ON PASSWORD_OBJECTS (ACCOUNTNAME, ADDRESS,
        (CASE
            WHEN POLICY_SET = MGMT_ID OR POLICY_SET = SSHKEY_MGMT_ID THEN 1
            ELSE 0
        END
        )
    );
    COMMIT;
END;
/

And the problem is that I get the error :

SQL Error [6550] [65000]: ORA-06550: line 8, column 5:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

Lastly, but not least I have to mention that the goal of this script is to ​create a limitation on table PASSWORD_OBJECTS (which will ensure that there will unicity for the combination of columns (ACCOUNTNAME, ADDRESS), with only one exception :

  • the case when 2 entries (lines) of the table have same combination of columns (ACCOUNTNAME, ADDRESS) and one entry has column POLICY_SET in (MGMT, SSHKEY_MGMT) and the other entry has column POLICY_SET not in (MGMT, SSHKEY_MGMT).

That would be the only case when two lines can have same values in combination (ACCOUNTNAME, ADDRESS).

Any ideas? I am open to other technical solution if you think my idea with the index is KO.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Table DDL and sample data would help in checking whether this does what you want, but a syntactically working version would be:

declare
    mgmt_id          number;
    sshkey_mgmt_id   number;
    create_index_sql varchar2(200);
begin
    select id into mgmt_id
    from   policy_sets
    where  policy_set_name = 'MGMT';

    select id into sshkey_mgmt_id
    from   policy_sets
    where  policy_set_name = 'SSHKEY_MGMT';

    create_index_sql :=
    'create unique index password_object_ui_1 on password_objects'||
    '( accountname, address'||
    ', (case when policy_set in ('||mgmt_id||','||sshkey_mgmt_id||') then 1 else 0 end) )';
    
    dbms_output.put_line(create_index_sql);
    
    execute immediate create_index_sql;
end;

which gives (with a bit of reformatting):

create unique index password_object_ui_1 on password_objects
( accountname
, address
, (case when policy_set in (123, 321) then 1 else 0 end) )

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply