The script below modifies all columns from VARCHAR2 BYTE to CHAR for all tables listed in the IN list. The script uses the USER_TAB_COLUMNS view. A log table has been created to record failures.
create table log_tbl (
table_name varchar2(30)
, column_name varchar2(30)
, msg varchar2(200)
, error_flag varchar2(1) default 'P') -- P for Pass and F for Fail.
/
SQL> select table_name, column_name, char_used
2 from user_tab_columns
3 where table_name in ('T1','T2')
4 /
TABLE_NAME COLUMN_NAME C
------------------------------ ------------------------------ -
T1 A B
T2 A B
SQL> declare
2 l_Err varchar2(200);
3 begin
4 for r in (select atc.table_name, atc.column_name, atc.data_length
5 from user_tab_columns atc -- You would probably use ALL_
6 left outer join Log_Tbl lt on (atc.Table_name = lt.Table_Name
7 and atc.Column_name = lt.Column_Name
8 and lt.Error_Flag = 'P')
9 where atc.data_type = 'VARCHAR2'
10 and atc.char_used = 'B'
11 and atc.Table_Name in ('T1', 'T2', 'T3')) loop
12
13 begin
14 execute immediate 'alter table ' || r.table_name
15 || ' modify '
16 || r.column_name
17 || ' varchar2('
18 || r.data_length
19 || ' char)';
20
21 insert into Log_tbl (Table_Name, Column_Name)
22 values (r.Table_Name, r.Column_Name);
23
24 exception
25 when others then
26 l_Err := sqlerrm;
27 insert into Log_tbl (Table_Name, Column_Name, Msg, Error_Flag)
28 values (r.Table_Name, r.Column_Name, l_Err, 'F');
29 end;
30
31 commit;
32
33 end loop;
34
35 end;
36 /
PL/SQL procedure successfully completed.
SQL> select table_name, column_name, char_used
2 from user_tab_columns
3 where table_name in ('T1','T2', 'T3')
4 /
TABLE_NAME COLUMN_NAME C
------------------------------ ------------------------------ -
T1 A C
T2 A C
SQL> select table_name,column_name,error_flag
2 from log_tbl;
TABLE_NAME COLUMN_NAME E
--------------- --------------- -
T1 A P
T2 A P
SQL> create table t3 (a varchar2(20) )
2 /
Table created.
SQL> insert into t3 (a) values ('Hello')
2 /
1 row created.
SQL> select table_name, column_name, char_used
2 from user_tab_columns
3 where table_name in ('T1','T2', 'T3');
TABLE_NAME COLUMN_NAME C
------------------------------ ------------------------------ -
T1 A C
T2 A C
T3 A B
Note the difference in the column char usage between T3 and the other tables given that T3 was created after the script was executed.
SQL> declare
2 l_Err varchar2(200);
3 begin
4 for r in (select atc.table_name, atc.column_name, atc.data_length
5 from user_tab_columns atc -- You would probably use ALL_
6 left outer join Log_Tbl lt on (atc.Table_name = lt.Table_Name
7 and atc.Column_name = lt.Column_Name
8 and lt.Error_Flag = 'P')
9 where atc.data_type = 'VARCHAR2'
10 and atc.char_used = 'B'
11 and atc.Table_Name in ('T1', 'T2', 'T3')) loop
12
13 begin
14 execute immediate 'alter table ' || r.table_name
15 || ' modify '
16 || r.column_name
17 || ' varchar2('
18 || r.data_length
19 || ' char)';
20
21 insert into Log_tbl (Table_Name, Column_Name)
22 values (r.Table_Name, r.Column_Name);
23
24 exception
25 when others then
26 l_Err := sqlerrm;
27 insert into Log_tbl (Table_Name, Column_Name, Msg, Error_Flag)
28 values (r.Table_Name, r.Column_Name, l_Err, 'F');
29 end;
30
31 commit;
32
33 end loop;
34
35 end;
36 /
PL/SQL procedure successfully completed.
SQL> select table_name, column_name, char_used
2 from user_tab_columns
3 where table_name in ('T1','T2', 'T3')
4 /
TABLE_NAME COLUMN_NAME C
--------------- --------------- -
T1 A C
T2 A C
T3 A C
The script uses the USER_TAB_COLUMNS view. If you modify the script to use the ALL_TAB_COLUMNS or the DBA_TAB_COLUMNS view, then the script would look as follows:
undefine schema_name
declare
l_Err varchar2(200);
begin
for r in (select atc.table_name, atc.column_name, atc.data_length
from all_tab_columns atc -- You would probably use ALL_
left outer join Log_Tbl lt on (atc.Table_name = lt.Table_Name
and atc.Column_name = lt.Column_Name
and lt.Error_Flag = 'P')
where atc.data_type = 'VARCHAR2'
and atc.char_used = 'B'
and atc.Table_Name in ('T1', 'T2', 'T3')
and atc.owner = upper('&&schema_name')) loop
begin
execute immediate 'alter table '|| upper('&&schema_name')
|| '.'
|| r.table_name
|| ' modify '
|| r.column_name
|| ' varchar2('
|| r.data_length
|| ' char)';
insert into Log_tbl (Table_Name, Column_Name)
values (r.Table_Name, r.Column_Name);
exception
when others then
l_Err := sqlerrm;
insert into Log_tbl (Table_Name, Column_Name, Msg, Error_Flag)
values (r.Table_Name, r.Column_Name, l_Err, 'F');
end;
commit;
end loop;
end;
/
The default bhaviour, when the usage is not specified explicitly, is to set each column to BYTE. The default behaviour can be altered by setting NLS_LENGTH_SEMANTICS, for example:
SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
Session altered.
SQL> DROP TABLE T1;
Table dropped.
SQL> DROP TABLE T2;
Table dropped.
SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
Session altered.
SQL> create table t1 (a varchar2(10));
Table created.
SQL> select table_name,column_name,char_used from user_tab_columns
2 where table_name = 'T1';
TABLE_NAME COLUMN_NAME C
---------- ----------- -
T1 A C
SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
Session altered.
SQL> create table t2 (a varchar2(10));
Table created.
SQL> select table_name,column_name,char_used from user_tab_columns
2 where table_name IN ('T2', 'T1');
TABLE_NAME COLUMN_NAME C
---------- ----------- -
T1 A C
T2 A B
