PROCEDURE convert_byte_to_char

CREATE OR REPLACE PROCEDURE convert_byte_to_char IS
  v_module_name    SGA_W_LOG.procedure_name%TYPE;
  v_msg_text       SGA_W_LOG.data%TYPE;
  v_sql            VARCHAR2(1000);    
    
  -- Cursor to find all VARCHAR2 columns defined as BYTE
  CURSOR byte_cols_cur IS
  SELECT table_name, column_name, data_length
    FROM user_tab_columns
   WHERE data_type = 'VARCHAR2'
     AND char_used = 'B'
     AND virtual_column = 'NO'
  ORDER BY table_name, column_name;
        
BEGIN
  v_module_name := 'convert_byte_to_char';
  -- Log the start of the procedure
  INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
  VALUES (v_module_name, 'Procedure started.', SYSDATE);
  COMMIT;
  FOR byte_cols_rec IN byte_cols_cur LOOP
    BEGIN
      -- Construct the dynamic DDL statement
      -- e.g., ALTER TABLE MY_TABLE MODIFY (MY_COL VARCHAR2(50 CHAR))
      v_sql := 'ALTER TABLE ' || DBMS_ASSERT.enquote_name(byte_cols_rec.table_name) || 
               ' MODIFY (' || DBMS_ASSERT.enquote_name(byte_cols_rec.column_name) || 
               ' VARCHAR2(' || byte_cols_rec.data_length || ' CHAR))';
           
      -- Execute the DDL
      EXECUTE IMMEDIATE v_sql;
            
      -- Log successful modification
      v_msg_text := 'Successfully altered ' || byte_cols_rec.table_name || '.' || byte_cols_rec.column_name || ' to VARCHAR2(' || byte_cols_rec.data_length || ' CHAR).';
                         
      INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
      VALUES (v_module_name, v_msg_text, SYSDATE);
            
      EXCEPTION
        WHEN OTHERS THEN
          -- Log any specific errors encountered per table/column so the loop can continue
          v_msg_text := 'ERROR altering ' || byte_cols_rec.table_name || '.' || byte_cols_rec.column_name ||': ' || SQLERRM;
                             
          INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
          VALUES (v_module_name, SUBSTR(v_msg_text, 1, 1500), SYSDATE);
      END;
        
      -- Commit the log entry for the current column
      COMMIT;
  END LOOP;
  -- Log completion
  INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
  VALUES (v_module_name, 'Procedure finished successfully.', SYSDATE);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- Catch-all for unexpected global failures
    ROLLBACK;
    v_msg_text := 'Unexpected Error: CRITICAL FAILURE: ' || SQLERRM;
    INSERT INTO sga_w_log (procedure_name, data, ts_last_modified)
    VALUES (v_module_name, SUBSTR(v_msg_text, 1, 1500), SYSDATE);
    COMMIT;
    RAISE;
END convert_byte_to_char;
/