InsightWays — Predictable Migration Strategy | Watch the Session

New GUI for SQLWays | Watch the Live Product Tour

IDM: New Way to Automate Data Migration | Watch the Session

Oracle to MySQL: everything you need to know

12 min read

September 15, 2023

—

Alexandr Kirpichny

Alexandr Kirpichny →

Expertise in legacy system modernization and database transformation

All articles by the author

Talk to expert

—

Oracle to MySQL: everything you need to know

In the world of databases, switching from Oracle to MySQL database is like moving from a well-established giant to a nimble contender. Oracle has been the go-to choice for big companies, but things are changing. This article is your guide to understand why and how this shift makes sense. We'll break down the reasons, walk you through the technical steps, and make sure you're ready to embrace Oracle to MySQL migration.

What is Oracle?

Oracle database is a powerful and widely-used relational database management system (RDBMS) developed by Oracle Corporation. It's designed to store, manage, and retrieve structured data efficiently. In simple terms, think of it as a sophisticated digital filing cabinet where organizations can store and organize their data in a structured manner.

Oracle database uses a structured query language called SQL to interact with the data it holds. This allows users to perform various operations like inserting, querying, updating, and deleting data within the database. What sets Oracle database apart is its ability to handle large amounts of data, ensure data integrity, and provide advanced security features to protect sensitive information.

It's widely utilized across industries for applications ranging from handling financial transactions, customer records, and inventory management to running complex analytical queries. Oracle database scalability and reliability make it a popular choice for businesses that require robust data management solutions to support their operations.

What is MySQL?

MySQL is a relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL).

Relational databases are digital stores that collect and organize data according to a relational model. The structure of tables in this model follows a strict logical structure, with rows and columns. A Relational Database Management System (RDBMS) consists of the tools used to create, manage, and query such a database.

In today's world, MySQL is an integral part of many popular software stacks, whether you are building customer-facing web applications or powerful, data-driven business-to-business applications. Due to its open-source nature, stability, and rich feature set, Facebook, Flickr, Twitter, Wikipedia, and YouTube have all chosen MySQL over other platforms due to its open-source nature, stability, and feature set.

Oracle to MySQL Migration: 2 easy methods

Our article examines two ways to migrate Oracle database to MySQL:

Manual migration. This migration is appropriate for small and simple databases
Automated migration using special tools. This type of migration is appropriate for databases with a large amount of sql code, including packages.

How to Set up Oracle to MySQL Migration Manually?

1. Begin by accessing your MySQL interface and initializing the ODBC Connector data source.

2. Connect the ODBC Data Source to MySQL.

3. Enter the necessary information to connect the database, user, and password for the Oracle Server. Give an appropriate ODBC connection name. To check the connection, click the Test button.

4. Input the requisite details, including database credentials, user identification, and Oracle Server password. Assign a fitting name to the ODBC connection. To check the connection, click the Test button.

SID_LIST_LISTENER =
&lsaquo;<Space>&rsaquo;(SID_LIST=
&lsaquo;<Space>&rsaquo;(SID_DESC=
&lsaquo;<Space>&rsaquo;(SID_NAME=test)
&lsaquo;<Space>&rsaquo;(ORACLE_HOME=C:oracleproduct11.2.0dbhome_1)
&lsaquo;<Space>&rsaquo;(PROGRAM=dg4odbc)
)
)

If the path specified isn’t your installation path, change the location of ORACLE HOME.

If you have any problems, type dg4odbc to see if the software is present or not. If you encounter a "Program not recognized" problem, make sure your path variable includes $ORACLE HOME/bin and restart the command line to verify.

5. The file of inittest.ora is generated automatically when a new SID is created. To change its name, use init + SID name. Set up the data source and SID for dg4odbc program. Add the commands to the inittest.ora file.

HS_FDS_CONNECT_INFO = test

6. Configure tnsnames.ora file (in the $ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ora.) by adding the statements.

TSLINK =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=)
)
(CONNECT_DATA=
(SID=the one created on the listener.ora file)
)
(HS=OK)
)

7. Reboot the Oracle Service and TNS Listener Service.

8. Log in to the Oracle database with credentials and create the database link: create public database link link2mysqltest connect to "oracle" identified by "oracle" using 'TSTLINK';

9. Test the link and click Finish.

10. Log in to MySQL and run the command.

select * from "xx_table"@link2mysqltest

Limitations of Manual Oracle to MySQL Migration

To migrate Oracle database to MySQL manually you should be aware of a set of limitations:

While the setup may seem straightforward, the intricacies of a successful migration demand profound Oracle administration skills. Lack of the expertise can result in numerous errors due to the multitude of configuration parameters that differ across various versions.
Even upon achieving a seemingly smooth Oracle database to MySQL migration, the real-world connection exposes numerous constraints and imperfections concerning executable SQL queries. Challenges emerge involving unsupported functions and data types in both source and target databases.

Oracle to MySQL migration using Ispirer Toolkit

SQLWays Wizard is an easy-to-use solution for heterogeneous database migrations. Its smart core helps to automate the migration of a database schema, including data, tables, stored procedures, functions, triggers, and views. It supports a vast amount of migration directions between legacy and modern RDBMS, including Oracle to MySQL migration.

One of the hallmarks of SQLWays Wizard is a flexible approach to customizing the tool with hundreds of options for any kind of migration projects.

1. Tables, indexes and data types

SQLWays Wizard automates the migration of Oracle data types to corresponding types in MySQL database. The tool contains settings that allow to specify the target types of data in MySQL.

Oracle MySQL

CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
);
create index "Table1_IDX" on "Table1"("AA")

2. Variables

In order to declare variables inside the function, the tool uses a DECLARE keyword. The DEFAULT keyword serves for assigning values to variables.

After Oracle to MySQL migration, the trunc function converts to a corresponding TRANCATE function.

Oracle MySQL

CREATE FUNCTION "DELAYREPORTMESSAGE" (fltseqno number,delcode varchar2) return varchar2 is
var_drm varchar2(4000) :='';
var_drm2 varchar2(4000) :='';
var_drm3 varchar2(4000) :='';
var_drm4 varchar2(4000) :='';
begin
SELECT trunc(a.delay_mts),
trunc(a.delay_mts/60),
trunc(a.delay_mts/60,5),
trunc(a.delay_mts/60,fltseqno)
INTO var_drm, var_drm2, var_drm3, var_drm4
FROM ekmis_flt_delay_dtls a;
return var_drm;
end;

3. Packages

MySQL syntax doesn’t contain packages, so the tool converts them to objects.

Oracle MySQL

CREATE OR REPLACE PACKAGE emp_mgmt AS
PROCEDURE remove_emp(employee_id NUMBER);
END emp_mgmt;
CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
PROCEDURE remove_emp (employee_id NUMBER) IS
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
END;
END emp_mgmt;

4. Cursors

Some changes SQLWays Wizard provides during Oracle to MySQL conversion:

For Oracle cursors the tool declares additional variables SWV_NO_DATA, SWV_CUR1_COL1, SWV_CUR1_TOTAL.
If a cursor doesn’t contain variables, then the tool uses a handler and SWV_NO_DATA variable.
Instead of FOR LOOP the tool uses FETCH. Values are written to variables SWV_CUR1_COL1 и SWV_CUR1_TOTAL.

Oracle MySQL

CREATE PROCEDURE PROC_A_T2 AS
v1 NUMBER;
v2 NUMBER;
CURSOR CUR1 IS
SELECT CASE WHEN UPPER (col2) IN ('Q') THEN col1 ||'Request'
WHEN UPPER (col2) IN ('R') THEN col1 ||'Response'
WHEN UPPER (col2) IN ('*') THEN col1 ||'Unknown'
ELSE col1 END col1, CNT TOTAL
FROM ( SELECT
col1, col2, COUNT (*) CNT
FROM A_T2
GROUP BY col1, col2)
GROUP BY col1, col2, CNT
ORDER BY col1;
BEGIN
v1 := 1;
IF v1 > 0 THEN
FOR CUR1_REC IN CUR1 LOOP
v2 := ROUND((CUR1_REC.TOTAL/v1)*100, 4);
END LOOP;
END IF;
END PROC_A_T2 ;

7. Sequences

Sequences also undergo certain changes after the migration from Oracle to MySQL. As MySQL syntax doesn’t have sequences, conversion requires creating additional objects. Particularly, a table for preserving values, functions for getting current values, functions for subsequent values and procedures which create sequence equivalents by calling it.

Oracle MySQL

create sequence "EXAMPLE_OF_SEQ" minvalue 1000 maxvalue 1000000000 cycle

call SWP_CrtSeq('EXAMPLE_OF_SEQ',1,1,1000000000,1000,1,null);
DELIMITER //
DROP PROCEDURE IF EXISTS SWP_CrtSeq;
//
create procedure SWP_CrtSeq(SeqN VARCHAR(50), SeqVal DECIMAL(30,0), SeqI BIGINT, SeqMx DECIMAL(30,0), SeqMn DECIMAL(30,0), SeqCc INT, SeqRest DECIMAL(30,0))
SWL_return:
begin
DECLARE sql_error INT;
DECLARE RaiserrorMsg VARCHAR(1000);
IF EXISTS (SELECT 1 FROM SWT_Sequence WHERE SeqName = SeqN) then
SET RaiserrorMsg = CONCAT(N'Sequence ', SeqN, N' already exists in the database.');
SIGNAL SQLSTATE '99999' SET MESSAGE_TEXT = RaiserrorMsg;
LEAVE SWL_return;
end if;
if SeqI > 9999999999999999999999999999 or SeqI < -99999999999999999999999999 or SeqMx > 9999999999999999999999999999 or SeqMx < -99999999999999999999999999
or SeqMn < -99999999999999999999999999 or SeqMn > 9999999999999999999999999999 or SeqRest > 9999999999999999999999999999 or SeqRest < -99999999999999999999999999
or SeqVal > 9999999999999999999999999999 or SeqVal < -99999999999999999999999999 then
SET RaiserrorMsg = N'A Sequence object definition value is invalid or out of range.';
SIGNAL SQLSTATE '99999' SET MESSAGE_TEXT = RaiserrorMsg;
LEAVE SWL_return;
end if;
if SeqMx < SeqMn then
SET RaiserrorMsg = N'MAXVALUE cannot be less than MINVALUE.';
SIGNAL SQLSTATE '99999' SET MESSAGE_TEXT = RaiserrorMsg;
LEAVE SWL_return;
end if;
if SeqRest > SeqMx or SeqRest < SeqMn or SeqVal > SeqMx or SeqVal < SeqMn then
SET RaiserrorMsg = N'Cannot set START value less than MINVALUE or greater than MAXVALUE.';
SIGNAL SQLSTATE '99999' SET MESSAGE_TEXT = RaiserrorMsg;
LEAVE SWL_return;
end if;
insert into SWT_Sequence(SeqName, SeqValue, SeqInc, SeqMax, SeqMin, SeqCyc, SeqRestart) values(SeqN, SeqVal, SeqI, SeqMx, SeqMn, SeqCc, SeqRest);
end;
//
DELIMITER ;
DROP TABLE IF EXISTS SWT_Sequence;
create table SWT_Sequence(SeqName varchar(50) primary key, SeqValue DECIMAL(30,0), SeqInc bigint, SeqMax DECIMAL(30,0), SeqMin DECIMAL(30,0), SeqCyc int, SeqRestart DECIMAL(30,0), SeqPrevRestart DECIMAL(30,0));
DELIMITER //
DROP FUNCTION IF EXISTS SWF_NextVal;
//
create function SWF_NextVal(SeqN varchar(50))
returns DECIMAL(30,0)
deterministic
begin
DECLARE SeqVal DECIMAL(30,0);
DECLARE RaiserrorMsg VARCHAR(1000);
if exists(select 1 from SWT_Sequence where SeqName = SeqN) then
select CASE WHEN SeqRestart is null THEN case when SeqValue = SeqMax and SeqCyc = 1 then SeqMin else SeqValue+SeqInc end
ELSE SeqRestart END INTO SeqVal from SWT_Sequence where SeqName = SeqN;
update SWT_Sequence set SeqValue = CASE WHEN SeqRestart is null THEN case when SeqValue = SeqMax and SeqCyc = 1 then SeqMin else SeqValue+SeqInc end
ELSE SeqRestart END,SeqRestart = null,SeqPrevRestart = CASE WHEN SeqRestart IS NULL THEN SeqPrevRestart ELSE SeqRestart END where SeqName = SeqN;
return SeqVal;
else
SET RaiserrorMsg = CONCAT(N'The specified sequence object ', SeqN, ' is not in the database.');
SIGNAL SQLSTATE '99999' SET MESSAGE_TEXT = RaiserrorMsg;
end if;
end;
//
DELIMITER ;
DELIMITER //
DROP FUNCTION IF EXISTS SWF_CurrVal;
//
create function SWF_CurrVal(SeqN varchar(50))
returns DECIMAL(30,0)
deterministic
begin
DECLARE SeqVal DECIMAL(30,0);
DECLARE RaiserrorMsg VARCHAR(1000);
if exists(select 1 from SWT_Sequence where SeqName = SeqN) then
select SeqValue INTO SeqVal from SWT_Sequence where SeqName = SeqN;
return SeqVal;
else
SET RaiserrorMsg = CONCAT(N'The specified sequence object ', SeqN, ' is not in the database.');
SIGNAL SQLSTATE '99999' SET MESSAGE_TEXT = RaiserrorMsg;
end if;
end;
//
DELIMITER ;

We understand that the migration journey can be intricate, riddled with challenges. Ispirer Toolkit offers an intelligent approach to converting Oracle databases to MySQL. It eliminates the complexities by automating the entire process, leaving no room for error or uncertainty. From data extraction to transformation and loading, every step is orchestrated seamlessly, ensuring a smooth and error-free transition.

Picture this: a user-friendly interface that empowers you to select the necessary settings. With a few clicks, you effortlessly connect your source database to the target destination. Once these parameters are in place, a simple press of the "migrate" button sets the process in motion. The tool takes care of the rest – sql code conversion, data extraction, data type mapping, schema conversion, and more, all executed with precision.

This tool transcends the limitations of manual migration, providing a level of consistency, accuracy, and speed that human-driven processes struggle to match. By embracing automation, you empower your team to focus on higher-level tasks, leveraging your expertise where it matters most.

Try Ispirer Toolkit for free with a 30-days free trial and check the effectiveness of automated Oracle to MySQL migration.

Table Of Content

What is Oracle?
What is MySQL?
Oracle to MySQL Migration: 2 easy methods
How to Set up Oracle to MySQL Migration Manually?
Limitations of Manual Oracle to MySQL Migration
Oracle to MySQL migration using Ispirer Toolkit