Take the 2-minute tour ×
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. It's 100% free, no registration required.

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:

VARCHAR2(20 BYTE)

new base:

VARCHAR2(20 CHAR)

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

2 Answers 2

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

DECLARE
  l_sql_stmt varchar2(1000);
BEGIN
  FOR c IN (SELECT *
              FROM user_tab_columns
             WHERE data_type = 'VARCHAR2'
               AND table_name = <<table name>>
               AND char_used = 'B')
  LOOP
    l_sql_stmt := 
       'ALTER TABLE table_name MODIFY( ' || 
          c.column_name || ' VARCHAR2( ' c.char_length || ' CHAR) )';
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
END;
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 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 at 21:06
    
Same end result. Cool –  Phil Jan 27 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 at 14:58
1  
@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 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
modify
(
  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

 
discard

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.