WinSQL » Knowledge base
|Subject:||Transferring, migrating, exporting data from MySQL to Oracle|
|Creation date:||7/27/10 3:17 PM|
|Last modified on:||7/27/10 3:56 PM|
Transferring data from
From time to time, businesses need to migrate data from one source to another. This is often a major challenge when such migration needs to be done between different types of databases. Moreover, some businesses require these data transfers periodically based on a schedule.
This article demonstrates how to migrate data between MySQL and Oracle databases, it talks about challenges faced when converting between different data types as well as transferring binary data.
Using WinSQL for data migration
WinSQL is a universal tool that can migrate data from any ODBC compliant data source to any ODBC compliant target.
Since WinSQL is ODBC compliant, you can connect to almost any database currently available in the market today. This includes heavy duty relational databases like Oracle, MS SQL Server, IBM DB2 and Sybase to single user file bases databases like MS Access, Paradox and DBase.
Data migration challenges
Here are some typical challenges that must be met when transferring data
- Data type conversion - Often databases use different set of data types, which differ in precision and names. It is important to match data types closely as possible to avoid data truncation
- Binary data transfer - Often text files are used to transfer data. This method is fine for alphanumeric data types but falls short if you have binary data types like images, audio, and video
- Logging - It is important to log errors that occur during data transfer. This way users can take appropriate action in case something goes wrong.
WinSQL supports two modes of data migration:
- Connected mode - when you have access to both MySQL and Oracle databases at the same time.
- Disconnected mode when the databases reside on the different network and cannot be connected simultaneously from one client machine
Connected mode is the fastest way to migrate data from one source to a target. In this mode WinSQL can connect to both MySQL and Oracle at the same time, run a SELECT queries against source database and issues INSERT statements against the target database.
The following image displays a screen shot connecting to both databases. The easiest way to initiate data transfer is to select the desired table from MySQL and drop in to Oracle window, or the other way around.
This invokes the Data Transfer wizard in WinSQL. Click here
to watch a video tutorial showing how to using WinSQL in this mode to transfer data.
Disconnected mode is useful when you cannot connect to both MySQL and Oracle at the same time. This requires two steps:
- Exporting data from MySQL, saving it to a temporary location
- Importing back into Oracle from the temporary location.
Historically, users have been using text files to import from the source and then insert data back into target. Typically, these text files contain data in CSV format, Tab delimited, or fixed length.
Although this technique may work in certain cases, it has its own problems:
- Custom code must be written to import/export data
- Cannot export binary data
- Carriage returns, line feeds, tab and other non-printable characters may not get transferred correctly
- Transferring large text value for one field becomes a challenge
WinSQL overcomes these challenges by creating the temporary file in a binary format, which not only handles the above mentioned problems but is generic enough to be used by any database. In WinSQL's language, this is called a DataBag. DataBags can not only store data, but also the design of a table. This way you can easily recreate the table in the target database. Click here
to watch a video tutorial on this subject.
Add a comment to this document
Do you have a helpful tip related to this document that you'd like to share
with other users? Please add it below. Your name and tip will appear at the
end of the document text.