Re: Converting LONG to CHAR

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 23 Nov 1999 10:30:53 -0500
Message-ID: <m6cl3skb03irj55bghcikc286n9odlivrg@4ax.com>

A copy of this was sent to gthollingsworth_at_gpu.com (if that email address didn't require changing) On Tue, 23 Nov 1999 14:35:11 GMT, you wrote:

>Is there an easier way to convert data from LONG to CHAR or VARCHAR2
>datatype other than using SQLLoader? Using Oracle 7.3.4. Any
>suggestion appreciated (but I think I'm stuck doing it this way).
>
>Thanks
>Jerry
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

As long as the LONG is shorter then 32k bytes, the following works in place:

rem table that has some longs...
tkyte_at_8.0> create table t ( x int, long_column long ); Table created.

tkyte_at_8.0> insert into t values ( 1, rpad( 'x', 3000, 'x' ) ); 1 row created.

rem scratch table used temporarily
ytkyte_at_8.0> create table tmp ( r rowid, vc varchar2(2000) );

Table created.

tkyte_at_8.0>
tkyte_at_8.0> begin

  2          for x in ( select rowid r, long_column from t ) loop
  3            insert into tmp values ( x.r, substr( x.long_column, 1, 2000 ) );
  4          end loop;
  5          update t set long_column = null;

  6 end;
  7 /

PL/SQL procedure successfully completed.

tkyte_at_8.0>
tkyte_at_8.0> alter table t modify long_column varchar2(2000);

Table altered.

tkyte_at_8.0>
tkyte_at_8.0> update t set long_column = ( select vc from tmp where r = t.rowid );

1 row updated.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Nov 23 1999 - 09:30:53 CST