Resizing every VARCHAR column in database table - Oracle 11g

Question

I have at least 65 VARCHAR columns in Table A that I would have to resize from X bytes to X char. I was hoping to find a simpler way than issue ALTER TABLE A MODIFY.. command 65 times.

Can anyone please help me out how I can do this is a faster way?

Answer 1

You can write a bit of dynamic SQL. Assuming that the table is in the current schema

DECLARE
  l_sql_stmt VARCHAR2(1000);
BEGIN
  FOR t IN (SELECT * FROM user_tab_cols WHERE table_name = 'A')
  LOOP
    l_sql_stmt := 'ALTER TABLE ' || t.table_name || ' MODIFY (' || 
                     t.column_name || ' varchar2(' || t.char_length || ' char))';
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
END;

Which you can see working below

SQL> ed
Wrote file afiedt.buf

  1  create table foo(
  2    col1 varchar2(10 byte),
  3    col2 varchar2(20 byte)
  4* )
SQL> /

Table created.

DECLARE
  l_sql_stmt VARCHAR2(1000);
BEGIN
  FOR t IN (SELECT * FROM user_tab_cols WHERE table_name = 'FOO')
  LOOP
    l_sql_stmt := 'ALTER TABLE foo MODIFY (' || 
                     t.column_name || ' varchar2(' || t.char_length || ' char))';
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
END;


SQL> desc foo;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(10 CHAR)
 COL2                                               VARCHAR2(20 CHAR)

Answer 2

You can try something ugly like that

 select 'ALTER TABLE '||table_name|
        ' MODIFY ('||column_name||' VARCHAR2('||char_length||' char));'
   from all_tab_cols
   where table_name='A' and
         datatype like 'VARCHAR2%';

copy the result grid and paste it in and run it in your prefered sql editor