WHENEVER SQLERROR EXIT SQL.SQLCODE

SET ECHO ON TERM ON FEED OFF PAGES 0 LINES 120 SERVEROUT ON

SELECT count(*)
FROM dba_objects O
JOIN dba_tab_columns C ON (O.OWNER=C.OWNER AND O.OBJECT_NAME=C.TABLE_NAME AND O.OBJECT_TYPE='TABLE')
WHERE INSTR( C.TABLE_NAME, '$' ) = 0
and O.OWNER='P_7236'
AND ( CHAR_USED IS NOT NULL AND CHAR_USED = 'B' )
AND O.CREATED > (SELECT S.CREATED+1/24 FROM ALL_USERS S WHERE S.USER_ID = 0)
/

alter session set events '01 trace name context forever, level 2'
/

BEGIN
  FOR R IN
  (
    SELECT
     'ALTER TABLE "' || C.OWNER || '"."' || C.TABLE_NAME || '" MODIFY "' || C.COLUMN_NAME || '" ' || C.DATA_TYPE || ' (' || C.DATA_LENGTH || ' CHAR)' ALT
    FROM dba_objects O
    JOIN dba_tab_columns C ON (O.OWNER=C.OWNER AND O.OBJECT_NAME=C.TABLE_NAME AND O.OBJECT_TYPE='TABLE')
    WHERE INSTR( C.TABLE_NAME, '$' ) = 0
    AND ( CHAR_USED IS NOT NULL AND CHAR_USED = 'B' )
    AND O.CREATED > (SELECT S.CREATED+1/24 FROM ALL_USERS S WHERE S.USER_ID = 0)
  )
  LOOP
    BEGIN
      EXECUTE IMMEDIATE R.ALT;
   EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -24005 THEN
          DBMS_OUTPUT.PUT_LINE( 'ORA-24005: ' || R.ALT );
        ELSIF SQLCODE = -14265 THEN
          DBMS_OUTPUT.PUT_LINE( 'ORA-14265: ' || R.ALT );
        ELSE
          DBMS_OUTPUT.PUT_LINE( R.ALT );
          RAISE;
        END IF;
    END;
  END LOOP;
END;
.

ALTER TABLE USR_SIATE.SIATEPRD MODIFY NOMMUNICIPIOELEITOTAL VARCHAR2(45 CHAR) NULL
/

SELECT count(*)
FROM dba_objects O
JOIN dba_tab_columns C ON (O.OWNER=C.OWNER AND O.OBJECT_NAME=C.TABLE_NAME AND O.OBJECT_TYPE='TABLE')
WHERE INSTR( C.TABLE_NAME, '$' ) = 0
AND ( CHAR_USED IS NOT NULL AND CHAR_USED = 'B' )
AND O.CREATED > (SELECT S.CREATED+1/24 FROM ALL_USERS S WHERE S.USER_ID = 0)
/

EXIT 0