Script para alterar tamanho de campo em todas as tabelas que ele exista.
BEGIN
DECLARE P_SCRIPT VARCHAR2(400);
NOVO_TAMANHO NUMBER(10);
CURSOR CUR_CONTROLE IS
SELECT 'ALTER TABLE ' || TABLE_NAME || ' MODIFY ' || COLUMN_NAME || ' VARCHAR2('||TO_CHAR(NOVO_TAMANHO)||')'
FROM USER_TAB_COLUMNS C
WHERE COLUMN_NAME LIKE '%CONTROLE%' --<<=== ALTERAR CAMPO
AND TABLE_NAME LIKE 'T%'
AND EXISTS(SELECT 1 FROM USER_TABLES T WHERE T.TABLE_NAME = C.TABLE_NAME)
AND DATA_LENGTH < NOVO_TAMANHO
AND DATA_TYPE IN ('VARCHAR2', 'CHAR', 'VARCHAR')
ORDER BY TABLE_NAME;
BEGIN
NOVO_TAMANHO := 12; --<<==== ALTERAR
OPEN CUR_CONTROLE;
LOOP
FETCH CUR_CONTROLE
INTO P_SCRIPT;
EXIT WHEN CUR_CONTROLE%NOTFOUND;
EXECUTE IMMEDIATE P_SCRIPT;
END LOOP;
CLOSE CUR_CONTROLE;
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20101, P_SCRIPT ||' erro: '||SQLERRM);
END;
END;
/
