Convert LONG to LOB on import(23ai)

Tables utilizing the LONG data type often present challenges when transitioning to LOB data types. Oracle Database 23ai introduces a powerful enhancement to simplify this process. With this release, the impdp utility allows users to convert LONG data types to LOB during data import, making the migration more efficient and streamlined. Oracle has added a new clause to the TRANSFORM parameter, named LONG_TO_LOB. To perform the conversion during import, you simply set this parameter to Y (TRANSFORM=LONG_TO_LOB:Y). Below, I will demonstrate how to use this feature. Step 1: Create a Table with a LONG Datatype Column: SQL> create table tbl (id number, full_name long); Table created. SQL> insert into tbl values(1,'Vahid Yousefzadeh'); 1 row created. SQL> commit; Commit complete. Step 2: Export the Table SQL> create directory dir2025 as '/home/oracle/dump'; Directory created. [oracle@OEL9 ~]$ expdp directory=dir2025 dumpfile=tbl.dmp tables=usef.tbl Export: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Mar 17 07:47:35 2025 Version 23.7.0.25.01 Copyright (c) 1982, 2025, Oracle and/or its affiliates. All rights reserved. Username: usef/a@OEL9:1521/derazkolah Connected to: Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems Starting "USEF"."SYS_EXPORT_TABLE_01": usef/@OEL9:1521/derazkolah directory=dir2025 dumpfile=tbl.dmp tables=usef.tbl Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "USEF"."TBL" 5.5 KB 1 rows Master table "USEF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ************************************************************** Dump file set for USEF.SYS_EXPORT_TABLE_01 is: /home/oracle/dump/tbl.dmp Job "USEF"."SYS_EXPORT_TABLE_01" successfully completed at Mon Mar 17 07:49:17 2025 elapsed 0 00:01:25 Step 3: Import the Table with the LONG-to-LOB Conversion SQL> create directory dir2025 as '/home/oracle/dump'; Directory created. [oracle@OEL9 ~]$ impdp directory=dir2025 dumpfile=tbl.dmp tables=usef.tbl remap_table=tbl:tbl_new TRANSFORM=LONG_TO_LOB:Y Import: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Mar 17 07:51:17 2025 Version 23.7.0.25.01 Copyright (c) 1982, 2025, Oracle and/or its affiliates. All rights reserved. Username: usef/a@OEL9:1521/derazkolah Connected to: Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems Master table "USEF"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "USEF"."SYS_IMPORT_TABLE_01": usef/@OEL9:1521/derazkolah directory=dir2025 dumpfile=tbl.dmp tables=usef.tbl remap_table=tbl:tbl_new TRANSFORM=LONG_TO_LOB:Y Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "USEF"."TBL_NEW" 5.5 KB 1 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "USEF"."SYS_IMPORT_TABLE_01" successfully completed at Mon Mar 17 07:52:26 2025 elapsed 0 00:01:04 Step 4: Verify the New Table SQL> desc tbl_new Name Null? Type ------------------ -------- --------- ID NUMBER FULL_NAME CLOB SQL> select * from tbl_new; ID FULL_NAME ---- ------------------------------ 1 Vahid Yousefzadeh In this demonstration, you’ve successfully converted the LONG data type to CLOB using the LONG_TO_LOB transformation during the import process. The FULL_NAME column in the new table tbl_new is now a CLOB instead of LONG, which provides more flexibility and functionality.

Apr 22, 2025 - 16:12

Tables utilizing the LONG data type often present challenges when transitioning to LOB data types. Oracle Database 23ai introduces a powerful enhancement to simplify this process. With this release, the impdp utility allows users to convert LONG data types to LOB during data import, making the migration more efficient and streamlined.

Oracle has added a new clause to the TRANSFORM parameter, named LONG_TO_LOB. To perform the conversion during import, you simply set this parameter to Y (TRANSFORM=LONG_TO_LOB:Y).

Below, I will demonstrate how to use this feature.

Step 1: Create a Table with a LONG Datatype Column:

SQL> create table tbl (id number, full_name long);
Table created.

SQL> insert into tbl values(1,'Vahid Yousefzadeh');
1 row created.

SQL> commit;
Commit complete.

Step 2: Export the Table

SQL> create directory dir2025 as '/home/oracle/dump';
Directory created.

[oracle@OEL9 ~]$ expdp directory=dir2025 dumpfile=tbl.dmp tables=usef.tbl
Export: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Mar 17 07:47:35 2025
Version 23.7.0.25.01
Copyright (c) 1982, 2025, Oracle and/or its affiliates.  All rights reserved.

Username: usef/a@OEL9:1521/derazkolah

Connected to: Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Starting "USEF"."SYS_EXPORT_TABLE_01":  usef/********@OEL9:1521/derazkolah directory=dir2025 dumpfile=tbl.dmp tables=usef.tbl
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "USEF"."TBL"                                  5.5 KB       1 rows
Master table "USEF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USEF.SYS_EXPORT_TABLE_01 is:
  /home/oracle/dump/tbl.dmp
Job "USEF"."SYS_EXPORT_TABLE_01" successfully completed at Mon Mar 17 07:49:17 2025 elapsed 0 00:01:25

Step 3: Import the Table with the LONG-to-LOB Conversion

SQL> create directory dir2025 as '/home/oracle/dump';
Directory created.

 [oracle@OEL9 ~]$ impdp directory=dir2025 dumpfile=tbl.dmp tables=usef.tbl remap_table=tbl:tbl_new TRANSFORM=LONG_TO_LOB:Y
Import: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Mar 17 07:51:17 2025
Version 23.7.0.25.01
Copyright (c) 1982, 2025, Oracle and/or its affiliates.  All rights reserved.

Username: usef/a@OEL9:1521/derazkolah

Connected to: Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Master table "USEF"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "USEF"."SYS_IMPORT_TABLE_01":  usef/********@OEL9:1521/derazkolah directory=dir2025 dumpfile=tbl.dmp tables=usef.tbl remap_table=tbl:tbl_new TRANSFORM=LONG_TO_LOB:Y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USEF"."TBL_NEW"                              5.5 KB       1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "USEF"."SYS_IMPORT_TABLE_01" successfully completed at Mon Mar 17 07:52:26 2025 elapsed 0 00:01:04

Step 4: Verify the New Table

SQL> desc tbl_new                       
 Name                Null?    Type      
 ------------------  -------- --------- 
 ID                           NUMBER    
 FULL_NAME                    CLOB      

SQL> select * from tbl_new;
  ID FULL_NAME
---- ------------------------------
   1 Vahid Yousefzadeh

In this demonstration, you’ve successfully converted the LONG data type to CLOB using the LONG_TO_LOB transformation during the import process. The FULL_NAME column in the new table tbl_new is now a CLOB instead of LONG, which provides more flexibility and functionality.