Re: Import - Convert char to varchar2 datatype?

A copy was also emailed to jjy_at_xnet.com

In article <70lgg4$fi5$1_at_flood.xnet.com>,   jjy_at_xnet.com wrote:
> I am importing a set of tables (via export/import) on Oracle 8.
> Most tables have character fields defined using the char(n) datatype
> instead of varchar2(n). In this case, there is no reason to use char over
> varchar2.
>
> I have redefined char(n) fields to be varchar2(n). Is there any way I
> can "squeeze out" the trailing white spaces on existing data? I tried
> exporting and then reimporting after I alter the tables to use varchar2(n),
> but the trailing blanks are not removed on existing data.
>
> Is there any way to automate this conversion process? Manually selecting
> the data and removing trailing white space is not an option. There are
> too many tables and too many columns.
>
> Thanks.
>
> Jim
>

UPDATE your_table SET former_char = RTRIM(former_char) ;

oops, I see you want to do this for all the tables. Can you generate a list of the columns that were changed, preferably with the name of the table containing it? Then use some dynamic SQL in a PL/SQL procedure to do the job. If the list is in some temporary table the procedure can just read the list and do the updates.

Something like this will do it:

CREATE OR REPLACE PROCEDURE trimColumns AS

   v_Cursor      NUMBER;
   v_sqlCommand  VARCHAR2(200);
   v_returnCount INTEGER;

CURSOR c_column IS

   select column_name,table_name
    from trim_columns_list;

  v_column trim_columns_list.column_name%TYPE;   v_table trim_columns_list.table_name%TYPE;

BEGIN
  OPEN c_column; --- the query cursor

  v_Cursor := DBMS_SQL.OPEN_CURSOR; --- the dynamic SQL cursor

  LOOP
    FETCH c_column into v_column,v_table;     EXIT WHEN c_column%NOTFOUND;

    v_sqlCommand := 'UPDATE '||v_table||' SET '||

                     v_column||' = RTRIM( '||v_column||' )'  ;

    DBSM_SQL.PARSE(v_Cursor, v_sqlCommand, DBMQ_SQL.V7);

    v_returnCount := DBMS_SQL.EXECUTE(v_Cursor);

  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(v_Cursor);
  CLOSE c_column;

  COMMIT;
END;     NOTE: the above is untested and has NO error handling,

          but it covers the basic idea.

 Drop me a line if you need help.

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own