There are several instances when your organization may want to convert data from a non-Oracle database or spreadsheet or a vendor application to an Oracle Database. People who have done this in any application will vouch that data conversion is one of the most daunting tasks of any software project. In this article, we will look at some of the proven data conversion methods and possibilities in Oracle Database 11g.

In the simplest scenario, data conversion involves converting any form of data to character delimited records and loading them into simple database tables using SQL Loader utilities or similar data loading tools. But this method often does not work in real world business applications. Data conversion in its basic form can be done in Oracle databases with DDL (Data Definition Language) statements, DML (Data Manipulation Language) statements, triggers, stored procedures, and by using scripts.

Data conversion with DDL statements can be done by transforming the current data into a format suitable for the new representation. DML statements are used in conjunction with triggers to manipulate data into required data types and values. Stored procedures are used to convert larger volumes of data, where data conversion has to be committed after a certain number of records to prevent saturation of database or system resources. In all these methods, duplicate records have to be deleted or removed from the source data files. For best practices, data conversion processes keep a conversion log as well as a bad records log for verification after the load process.

In older versions of Oracle databases, users tend to rely on a combination of above methods and internal data loading utilities to convert and load external data. In recent versions from Oracle 9i, Oracle has improved the utilities and functions available for this purpose. Oracle has explicit and implicit data conversion techniques. Oracle does explicit data conversions using SQL conversion functions.  Explicit conversion functions convert a value from one data type (input data type) to another data type (output data type). For implicit data conversions, Oracle automatically converts a field from one data type to another. When some user-defined data types cannot be implicitly converted, we use explicit data conversion techniques.

Oracle has its own rules for choosing explicit or implicit data conversion techniques. In most scenarios, Oracle wants you to use explicit conversions over implicit or automatic conversions for the following reasons. When you use implicit data type conversions, the data conversion and loading process will have an overall negative impact on database performance. Implicit data conversion will not work under many of the common conversion scenarios as tested by Oracle. The algorithms used for data conversion tend to vary for database releases and hence the blind usage of implicit data conversion could result in unwarranted result sets. When you use explicit data conversion techniques utilizing Oracle functions, you are actually using SQL statements that are tested, easier to understand, and work across versions. The end result for explicit data conversions is hence consistent over implicit or automatic conversions.

For more information on Oracle Data Conversion methods and functions, refer to your database version specific Oracle SQL reference guide. If you are really unsure or want to get vendor provided tools, evaluate these vendors: Altova, Pervasive Software, SiMX, SwisSQL and similar ones for the tool that best matches your enterprise architecture needs.

About the Author:

Dr. Arun Kumar R., data architect at Cingular Wireless, has over a decade of experience in Oracle technologies. He has authored two Oracle Database 10g books and over 50 technical papers. He can be reached at arundbta@dbatrends.com.