Re: Converting tables from CHAR to VARCHAR2

After the conversion I would go through and rtrim any trailing spaces.

Kevin Toepke (ktoepke_at_infinet.com) wrote:
:> In article <31057888.54D_at_cincom.com>, Brian M. Biggs <bbiggs_at_cincom.com> wrote:
:> >We have 250+ tables in our Oracle 7.1 application, and they all use CHAR
:> >datatypes instead of VARCHAR2. We are now looking at converting them to
:> >VARCHAR2, and were wondering what the easiest way is to do this.
 

:> Here is a packaged procedure I wrote that changes the data-type of existintg
:> columns from CHAR to VARCHAR2 (or vice-versa) and rtrims if converting from
:> CHAR...
 
:> example run:
:> SQL> exec tbl_change_datatype('JOEUSER', '%', 'CHAR', 'VARCHAR2');
 

:> as it does 1 alter table and 1 update statement per COLUMN, it may take a
:> while to run..
 

:> Hope this helps.
:> Kevin Toepke (ktoepke_at_infinet.com)
:> ----
 

:> CREATE OR REPLACE PROCEDURE tbl_change_datatype (
:> i_owner in varchar2
:> ,i_table_name in varchar2
:> ,i_org_datatype in varchar2
:> ,i_new_datatype in varchar2
:> ) IS
:> Vnew_datatype varchar2(20) := upper(i_new_datatype);
:> Vorg_datatype varchar2(20) := upper(i_org_datatype);
 

:> cursor column_list is
:> select table_name, column_name, data_length
:> from all_tab_columns
:> where owner = upper(i_owner)
:> and table_name like upper(i_table_name)
:> and data_type = Vorg_datatype;
 

:> Valter_stmt varchar2(2000);
:> Vupdate_stmt varchar2(2000);
:> Vcursor_id integer;
:> Vrow_count integer;
:> BEGIN
:> if (Vorg_datatype not in ('CHAR', 'VARCHAR', 'VARCHAR2')) then
:> dbms_output.put_line('Only CHAR and VARCHAR2 datatypes are suppored');
:> raise_application_error(-20106, 'Only CHAR and VARCHAR2 datatypes are suppored');
:> end if;
 

:> if (Vnew_datatype not in ('CHAR', 'VARCHAR', 'VARCHAR2')) then
:> dbms_output.put_line('Only CHAR and VARCHAR2 datatypes are suppored');
:> raise_application_error(-20105, 'Only CHAR and VARCHAR2 datatypes are suppored');
:> end if;
 

:> for db_column in column_list loop
:> dbms_output.put_line('Modifying ' || db_column.table_name ||
:> '.' || db_column.column_name);
 

:> Vcursor_id := dbms_sql.open_cursor;
:> Valter_stmt := 'alter table ' || db_column.table_name || ' ' ||
:> 'modify ' || db_column.column_name || ' ' ||
:> Vnew_datatype || '(' || db_column.data_length || ')';
:> dbms_sql.parse(Vcursor_id, Valter_stmt, dbms_sql.v7);
:> Vrow_count := dbms_sql.execute(Vcursor_id);
:> dbms_sql.close_cursor(Vcursor_id);
 

:> if (Vnew_datatype in ('VARCHAR', 'VARCHAR2')) then
:> Vupdate_stmt := 'update ' || db_column.table_name || ' ' ||
:> 'set ' || db_column.column_name || ' = ' ||
:> ' rtrim(' || db_column.column_name || ')';
:> Vcursor_id := dbms_sql.open_cursor;
:> dbms_sql.parse(Vcursor_id, Vupdate_stmt, dbms_sql.v7);
:> Vrow_count := dbms_sql.execute(Vcursor_id);
:> dbms_sql.close_cursor(Vcursor_id);
:> end if;
:> end loop;
:> END;
:> ----

--
This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.