I've used Oracle 11g and for some of the tables, used below command to create a unique index on specific column which ignores all white space.
CREATE UNIQUE INDEX UK_LOCATION_NAME ON LOCATION(UPPER(REGEXP_REPLACE("FARSI_NAME",'\s+','')));
Recently, The Oracle database is updated to 12c and executing the mentioned command raise an error:
[2019-06-08 19:44:08] [42000][1743] ORA-01743: only pure functions can be indexed
How can define a unique index which ignores white spaces(spaces, tab, ...)?
It seems the reason you were allowed to use REGEXP_REPLACE in a function based index in Oracle 11g was that it was a bug only in Oracle 11g and probably in 12.1 as well. It has been fixed since Oracle 12.2 and hence it won't allow you to create an index that uses REGEXP_REPLACE directly. The reason being it's a Non-deterministic function.
A similar issue exists with CHECK constraint too and has been discussed in detail over this post.
In your case, a simpler approach using REPLACE should be sufficient if you're only replacing spaces.
CREATE UNIQUE INDEX UK_LOCATION_NAME
ON LOCATION(UPPER(replace("FARSI_NAME",' ')));
The Other option to get around this problem when your replacement pattern is complex is to use an alternative function that's DETERMINISTIC. This is a workaround and may not be efficient for complex scenarios.
create or replace function my_regex_rep(txt_in VARCHAR2)
return VARCHAR2 DETERMINISTIC IS
BEGIN
return regexp_replace(txt_in,'\s+','');
END;
/
Now, you are allowed to use this function in the INDEX.
CREATE UNIQUE INDEX UK_LOCATION_NAME ON
LOCATION(UPPER(my_regex_rep("FARSI_NAME")));
Testing
INSERT INTO LOCATION(FARSI_NAME) values('ABCD EFGH');
1 row inserted.
INSERT INTO LOCATION(FARSI_NAME) values(' ABCD efgh ');
--spaces
ORA-00001: unique constraint (HR.UK_LOCATION_NAME) violated
INSERT INTO LOCATION(FARSI_NAME) values('ABCD EFGh');
--tab
ORA-00001: unique constraint (HR.UK_LOCATION_NAME) violated