Re: Converting tables from CHAR to VARCHAR2

From: Kevin Toepke <ktoepke_at_infinet.com>
Date: 1996/02/01
Message-ID: <4erkpp$5v_at_rigel.infinet.com>#1/1

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;

---

Received on Thu Feb 01 1996 - 00:00:00 CET