Re: Convert LONG Fields to Varchar2(4000)

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 29 May 2002 05:16:35 -0700
Message-ID: <ad2gr302bjc@drn.newsguy.com>

In article <d1a2ec09.0205290350.7e8c47ed_at_posting.google.com>, orakin_at_hotmail.com says...
>
>Hello all,
>
>I have a table in 7.3.4 that has a long field because the data was
>just about 3500 characters long. Now we are upgrading to 8.1.7 and I
>want to change all the long fields to varchar2(4000) and insert the
>data. Can anyone tell me how to do this?
>
>Thanks.

Export/Import can do this easily:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( x int, y long );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 1, rpad( '*', 3500, '*' ) );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 2, rpad( '*', 3500, '*' ) );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 3, rpad( '*', 3500, '*' ) );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> !exp userid=/ tables=t

Export: Release 8.1.7.3.0 - Production on Wed May 29 08:11:29 2002

Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production With the Partitioning option
JServer Release 8.1.7.3.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                              T          3 rows exported

Export terminated successfully without warnings.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( x int, y varchar2(4000) );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> !imp userid=/ tables=t ignore=y

Import: Release 8.1.7.3.0 - Production on Wed May 29 08:11:30 2002

Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production With the Partitioning option
JServer Release 8.1.7.3.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set . importing OPS$TKYTE's objects into OPS$TKYTE

. . importing table                            "T"          3 rows imported

Import terminated successfully without warnings.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Wed May 29 2002 - 07:16:35 CDT