text: whenever sqlerror exit sql.sqlcode
  set verify off
  
  BEGIN EXECUTE IMMEDIATE 'ALTER TABLE T_TAB_PRIVS_F ADD (TABLE_NAME_COMP VARCHAR2(128))'; EXCEPTION WHEN OTHERS THEN NULL; END;
  /
  
  DECLARE
    VNAME VARCHAR2(128);
    V_IS_11204 BOOLEAN;
  
    FUNCTION fc_adapt_type (V_STR_IN IN VARCHAR2) RETURN VARCHAR2 IS
       V_FINAL_PART varchar2(30);
    begin
       -- Ira truncar o código até o início da parte de dígito em X casas e concatenar o restante, variando o tamanho de acordo com o tamanho da própria string final e qtd de dígitos.
       -- Ex: PERFORMED_PROCEDURE_STE123_T -> PERFORMED_PROCEDURE123_T (24) | PREDICATES_DEFINITIO499_COLL -> PREDICATES_DEFIN499_COLL (24) | MEDIA_STORAGE_SOP_INSTA88_T -> MEDIA_STORAGE_SOP_I88_T (23)
       IF LENGTH(V_STR_IN)>23 THEN
         V_FINAL_PART := REGEXP_SUBSTR(V_STR_IN,'[[:digit:]]+_(T|COLL)$');
         RETURN SUBSTR(V_STR_IN,1,LEAST(24-(3-LENGTH(SUBSTR(V_FINAL_PART,1,INSTR(V_FINAL_PART,'_')-1))),LENGTH(V_STR_IN))-LENGTH(V_FINAL_PART)) || V_FINAL_PART;
       ELSE
         RETURN V_STR_IN;
       END IF;
    end;
  
    FUNCTION fc_remove_digits (V_STR_IN IN VARCHAR2) RETURN VARCHAR2 IS
    begin
       RETURN REGEXP_REPLACE(V_STR_IN,'[[:digit:]]+',''); -- Remove digits
    end;
  
  BEGIN
    FOR I IN (SELECT T_TAB_PRIVS_F.ROWID,T_TAB_PRIVS_F.* FROM T_TAB_PRIVS_F)
     LOOP
      IF (I.ORAVERSION = '11.2.0.4') THEN
        V_IS_11204 := TRUE;
      ELSE
        V_IS_11204 := FALSE;
      END IF;
      VNAME := '';
      IF I.OWNER IN ('MDSYS','ORDSYS','SYS','XDB') AND REGEXP_LIKE(I.TABLE_NAME,'^([[:alpha:]]|_|-)+[[:digit:]]+_(T|COLL)$') AND (I.TYPE = 'TYPE' OR V_IS_11204) AND I.GRANTEE IN ('XDB','PUBLIC') THEN
        VNAME := fc_remove_digits(fc_adapt_type(I.TABLE_NAME));
      ELSIF I.OWNER = 'XDB' AND REGEXP_LIKE(I.TABLE_NAME,'^X\$PT.*') AND (I.TYPE = 'TABLE' OR V_IS_11204) AND I.GRANTEE IN ('DBA','SYSTEM') THEN
        VNAME := REGEXP_REPLACE(I.TABLE_NAME,'^X\$PT.*','X$PT');
      ELSIF I.OWNER = 'SYS' AND REGEXP_LIKE(I.TABLE_NAME,'^QT([[:digit:]])+_BUFFER$') AND (I.TYPE = 'VIEW' OR V_IS_11204) THEN
        VNAME := REGEXP_REPLACE(I.TABLE_NAME,'^QT([[:digit:]])+_BUFFER$','QT_BUFFER');
      ELSIF I.OWNER = 'SYS' AND REGEXP_LIKE(I.TABLE_NAME,'^SYST.*==$') AND (I.TYPE = 'TYPE' OR V_IS_11204) AND I.GRANTEE = 'PUBLIC' THEN
        VNAME := REGEXP_REPLACE(I.TABLE_NAME,'^SYST.*==$','SYST==');
      ELSIF I.OWNER = 'XDB' AND REGEXP_LIKE(I.TABLE_NAME,'^SYS_NT') AND (I.TYPE = 'TABLE' OR V_IS_11204) AND I.GRANTEE = 'SELECT_CATALOG_ROLE' THEN
        VNAME := REGEXP_REPLACE(I.TABLE_NAME,'^SYS_NT.*','SYS_NT');
      ELSIF I.OWNER = 'XDB' AND REGEXP_LIKE(I.TABLE_NAME,'^X\$(NM|PT|QN)') AND (I.TYPE = 'TABLE' OR V_IS_11204) AND I.GRANTEE = 'SELECT_CATALOG_ROLE' THEN
        VNAME := REGEXP_REPLACE(I.TABLE_NAME,'^(X\$(NM|PT|QN)).*','\1');
      END IF;
      IF VNAME IS NOT NULL THEN
        UPDATE T_TAB_PRIVS_F
        SET TABLE_NAME_COMP = VNAME
        WHERE ROWID = I.ROWID;
      END IF;
    END LOOP;
    COMMIT;
  END;
  /
  
  whenever sqlerror continue
  set verify on
  