Changing a data type across the schema in the oracle

Question

Good afternoon ! I need to alter some kind of data from all my schema, I have the varchar2 size 80, and I’d like to change it to 100, but this all at once... it’s possible ?

Answer 1

--

--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

--SCRIPTING

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

--EXECUTING

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 RECOMMENDING A SAVE BEFORE AND IF POSSIBLE BEFORE EXECUTION --IN A TEST ENVIRONMENT