How to ensure unique index names when creating indexes for FKs with PL/SQL?

I'm trying to write a procedure that automatically creates indexes for foreign keys that don't have one yet. I created a function called SNK_VERIFICA_NOME_IDX which is supposed to ensure the index name is unique by appending a numeric suffix if the name already exists.

Here's the function and procedure:

CREATE OR REPLACE FUNCTION SNK_VERIFICA_NOME_IDX(P_INDICE VARCHAR2)
RETURN VARCHAR2
IS
  P_COUNT INT;
  P_QTD INT;
  P_NOVONOME VARCHAR2(100);
BEGIN
  P_NOVONOME := P_INDICE;
  SELECT COUNT(1) INTO P_COUNT FROM USER_INDEXES WHERE INDEX_NAME = P_INDICE;      
  IF P_COUNT <> 0 THEN
    P_QTD := 1;        
    WHILE (P_COUNT <> 0)
    LOOP
      P_NOVONOME := P_INDICE || P_QTD;
      SELECT COUNT(1) INTO P_COUNT FROM USER_INDEXES WHERE INDEX_NAME = P_INDICE || P_QTD;          
      P_QTD := P_QTD + 1;
    END LOOP;
  END IF;  
  RETURN P_NOVONOME;
END;
/

CREATE OR REPLACE PROCEDURE STP_GERA_INDICES_FKS
AS
BEGIN
DECLARE
  P_COUNT INT;
  P_COMANDO VARCHAR2(4000);
  P_TABELA VARCHAR2(50);
  P_INDICE VARCHAR2(50);
  P_COLUNAS VARCHAR2(1000);
  P_QTD INT;
  CURSOR CURIDX IS
  SELECT TABLE_NAME, SUBSTR(TABLE_NAME,1,10) || '_IDX_FK_' || SUBSTR(REPLACE(FK_COLUMNS,',','_'),1,10) AS INDEX_NAME, FK_COLUMNS AS INDEX_COLUMNS FROM
  (SELECT CASE WHEN B.TABLE_NAME IS NULL THEN 'unindexed' ELSE 'indexed' END AS STATUS,
          A.TABLE_NAME, A.CONSTRAINT_NAME, A.FK_COLUMNS, B.INDEX_NAME, B.INDEX_COLUMNS
     FROM (SELECT A.TABLE_NAME, A.CONSTRAINT_NAME,
                  LISTAGG(A.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY A.POSITION) FK_COLUMNS
             FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
            WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
              AND B.CONSTRAINT_TYPE = 'R'
              AND A.TABLE_NAME LIKE 'T%'
         GROUP BY A.TABLE_NAME, A.CONSTRAINT_NAME) A,
          (SELECT TABLE_NAME, INDEX_NAME,
                  LISTAGG(C.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY C.COLUMN_POSITION) INDEX_COLUMNS
             FROM USER_IND_COLUMNS C
            WHERE C.TABLE_NAME LIKE 'T%'
         GROUP BY TABLE_NAME, INDEX_NAME) B
    WHERE A.TABLE_NAME = B.TABLE_NAME(+)
      AND B.INDEX_COLUMNS(+) LIKE A.FK_COLUMNS || '%'
    ORDER BY 1 DESC, 2) X
  WHERE STATUS = 'unindexed';
BEGIN
  OPEN CURIDX;
  LOOP
    FETCH CURIDX INTO P_TABELA, P_INDICE, P_COLUNAS;
    EXIT WHEN CURIDX%NOTFOUND;
    P_INDICE := SNK_VERIFICA_NOME_IDX(P_INDICE);
    P_COMANDO := 'CREATE INDEX ' || P_INDICE || ' ON ' || P_TABELA || '(' || P_COLUNAS || ') tablespace SANKIND;';
    DBMS_OUTPUT.PUT_LINE(P_COMANDO);
  END LOOP;
  CLOSE CURIDX;
END;
END;
/

The problem is that even with this logic, the code still attempts to create two indexes with the same name:

SQL> CREATE INDEX XXXX_IDX_FK_XXXX ON XXXX(XXXXX);
Index created.

SQL> CREATE INDEX XXXX_IDX_FK_XXXX ON XXXX(XXXXX);
ERROR at line 1:
ORA-00955: name is already used by an existing object

Clearly, the function is not correctly ensuring unique index names. 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?

Edit: the problem is that I am using dbms_output.put_line.

The reason why using DBMS_OUTPUT.PUT_LINE doesn’t work in my case is because it only prints the SQL command as a string — it does not execute the statement.

As a result, the index is never actually created, so the Oracle data dictionary (e.g., USER_INDEXES) is not updated. My function SNK_VERIFICA_NOME_IDX, which checks if the index name already exists, keeps returning that the name is available — even though you've already "printed" a command with that name — because the command wasn't executed.

In contrast, when I use EXECUTE IMMEDIATE, the index is actually created in the database, which updates the data dictionary immediately. This allows your name-checking logic to work correctly on the next iteration.