ORA-20001: DUPLICATE_ALIAS_ERROR with ORDS RESTFul Services (Doc ID 2485630.1)

 When trying to re-use an ORDS based RESTFul service object alias the request fails even after deleting object.

Steps to reproduce:

Step -1: The table / view EMP is rest service enabled under alias ‘test’.
BEGIN
ORDS.enable_object(
p_enabled => TRUE,
p_schema => 'HR',
p_object => 'emp',
p_object_type => 'TABLE',
p_object_alias => 'test',
p_auto_rest_auth => FALSE );
COMMIT;
END;

Step-2: Remove the table / view for EMP.
           Create a new table / view called EMP2

Step-3: Enable the table / view for EMP2 with the same alias ‘test’:

BEGIN
ORDS.enable_object(
p_enabled => TRUE,
p_schema => 'HR',
p_object => 'emp2',
p_object_type => 'TABLE',
p_object_alias => 'test',
p_auto_rest_auth => FALSE );
COMMIT;
END;

This gives the next error:
error report -
ORA-20001: DUPLICATE_ALIAS_ERROR
ORA-06512: in "ORDS_METADATA.ORDS_INTERNAL", regel 293
ORA-06512: in "ORDS_METADATA.ORDS", regel 306
ORA-06512: in regel 5

In the view USER_ORDS_ENABLED_OBJECTS ( ORDS_METADATA ), we see that the association with the old view (even when it is deleted) is still active and causing the constraint error..
select * from USER_ORDS_ENABLED_OBJECTS;

- If we disable the rest service, it will have a status DISABLED in the view, but it will produce the same error.
- Dropping the Rest Service functionality on the schema level and enabling it again, resolves the problem.
  However, this causes the relations to be cleared in USER_ORDS_ENABLED_OBJECTS, so all REST services under this schema are lost with this workaround.

Changes

 Dropping a table / view and creating a table / view with a different name and assigning the same ORDS alias to the new database object.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.