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  : 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.
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 🙂