Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm migrating a database and i need to change the "container", i don't exactly how to name it. I give an example to keep it clear :

old base:


new base:


I can do it individually with alter...modify query but it takes so much time to prepare the queries cause the tables have a hundred or more columns. Is there any scripting way or a query that changes all the byte containers with char ?

share|improve this question
up vote 3 down vote accepted

You can use a bit of dynamic SQL. Assuming that you want to modify every VARCHAR2 column in one table

  l_sql_stmt varchar2(1000);
              FROM user_tab_columns
             WHERE data_type = 'VARCHAR2'
               AND table_name = <<table name>>
               AND char_used = 'B')
    l_sql_stmt := 
       'ALTER TABLE table_name MODIFY( ' || 
          c.column_name || ' VARCHAR2( ' c.char_length || ' CHAR) )';
    EXECUTE IMMEDIATE l_sql_stmt;
share|improve this answer
You might want a and char_length<>data_length in your query so that you don't try and alter existing VARCHAR2(XX CHAR) columns. – Phil Jan 27 '14 at 20:25
@Phil - If there can be xx char columns, I'd prefer to use the char_used column. Updated my answer. – Justin Cave Jan 27 '14 at 21:06
Same end result. Cool – Phil Jan 27 '14 at 21:15
Thanks for your answer, looks really nice. Could you tell how to execute it on SQL*PLUS or Oracle SQL developper cause i obtained the following error executing your query: Error in line 11 END LOOP Error report: Comand unknown – Jorge Vega Sánchez Jan 29 '14 at 14:58
@JorgeVegaSánchez - I posted a snippet of PL/SQL, not a complete block. I've updated my answer to be a complete anonymous PL/SQL block (though you'd obviously need to replace the <<table name>> with the actual table name. – Justin Cave Jan 29 '14 at 17:24

You can modify multiple columns at once in the modify clause. For example, given:

create table modifyme
  col1 VARCHAR2(20 BYTE),
  col2 VARCHAR2(20 BYTE),
  col3 VARCHAR2(20 BYTE),
  col4 VARCHAR2(20 BYTE)

You can execute:

alter table modifyme
  col1 VARCHAR2(20 CHAR),
  col2 VARCHAR2(20 CHAR),
  col3 VARCHAR2(20 CHAR),
  col4 VARCHAR2(20 CHAR)

Easily scriptable using a similar technique to Justins.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.