Changing a data type across the schema in oracle

Navigation
2

Good afternoon! I need to change a data type of my entire schema, I have varchar2 80, and I would like to change it to 100, but this all at once ... is it possible?

    
asked by anonymous 07.09.2017 / 22:06

1 answer

2

-

- LOCATE ALL COLUMNS OF TYPE VARCHAR2 AND SIZE 80 AND CHANGE TO 100

- LIST THESE COLUMNS 

SELECT *
FROM USER_TAB_COLUMNS
WHERE DATA_TYPE = 'VARCHAR2'
AND   DATA_LENGTH = 80

- MOUNTING A SCRIPT 

SELECT 'ALTER TABLE ' || TABLE_NAME || ' MODIFY ' || COLUMN_NAME || ' ' || DATA_TYPE || '(100)'
FROM USER_TAB_COLUMNS
WHERE DATA_TYPE = 'VARCHAR2'
AND   DATA_LENGTH = 80

- PERFORMING 

BEGIN
  FOR R IN (SELECT 'ALTER TABLE ' || TABLE_NAME || ' MODIFY ' || COLUMN_NAME || ' ' || DATA_TYPE || '(100)'
            FROM USER_TAB_COLUMNS
            WHERE DATA_TYPE = 'VARCHAR2'
            AND   DATA_LENGTH = 80) LINHA
  LOOP
    EXECUTE IMMEDIATE (R.LINHA);
  END LOOP;
END;

- generalizing 

CREATE OR REPLACE PROCEDURE ALTERA_TAMANHO(POWNER IN CHAR,
                                           PTAMDE NUMBER,
                                           PTAMPARA NUMVER) IS
BEGIN
  FOR R IN (SELECT 'ALTER TABLE ' OWNER || '.' || TABLE_NAME || ' MODIFY ' || COLUMN_NAME || ' ' || DATA_TYPE || '(' || PTAMPARA || ')'
                FROM DBA_TAB_COLUMNS
                WHERE DATA_TYPE = 'VARCHAR2'
                AND   DATA_LENGTH = PTAMDE) LINHA
  LOOP
    EXECUTE IMMEDIATE (R.LINHA);
  END LOOP;
END;

- TYPE OF OPERATION THAT RECOMMENDS A SAVE BEFORE AND IF POSSIBLE EXECUTION BEFORE --THE TEST ENVIRONMENT

    
08.09.2017 / 15:30
How to report data in a vector What is the problem with these hash algorithms?