Reports

Can someone help me identify what’s wrong with the logic in the SNK_VERIFICA_NOME_IDX function or the way it’s being used in the procedure?

You are using COUNT to count items in the data dictionary but you are not modifying the data dictionary so when a duplicate name is checked the duplication is not found because the data dictionary has not changed.

For example:

CREATE TABLE TABCDEFGHI1 (id NUMBER PRIMARY KEY);

CREATE TABLE TABCDEFGHI2 (
  t_id NUMBER
       PRIMARY KEY,
  id   NUMBER
       REFERENCES TABCDEFGHI1(id)
);

CREATE TABLE TABCDEFGHI3 (
  id NUMBER
     REFERENCES TABCDEFGHI1(id)
     REFERENCES TABCDEFGHI2(t_id)
);

Then the output is:

CREATE INDEX TABCDEFGHI_IDX_FK_ID ON TABCDEFGHI2(ID) tablespace SANKIND;
CREATE INDEX TABCDEFGHI_IDX_FK_ID ON TABCDEFGHI3(ID) tablespace SANKIND;
CREATE INDEX TABCDEFGHI_IDX_FK_ID ON TABCDEFGHI3(ID) tablespace SANKIND;

All 3 indexes are given identical names.

If you want to track duplicates, without changing the data dictionary, then the you need to maintain the state of the number of counts you have found for each index (probably in an associative array or something similar) and increment your local data structure when you generate each index name.

fiddle

79710747