Oracle to MySQL: everything you need to know

Alexandr Kirpichny

Chief Product Officer, Ispirer Systems

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).

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.

 
 
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")
 

 
	CREATE TABLE products
(
   product_id INT not null,
   supplier_id INT 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.

 
	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;
 

 
	CREATE FUNCTION       DELAYREPORTMESSAGE(fltseqno INT,delcode VARCHAR(4000)) RETURNS VARCHAR(4000) 
begin
   DECLARE var_drm VARCHAR(4000) DEFAULT '';
   DECLARE var_drm2 VARCHAR(4000) DEFAULT '';
   DECLARE var_drm3 VARCHAR(4000) DEFAULT '';
   DECLARE var_drm4 VARCHAR(4000) DEFAULT '';
   SELECT TRUNCATE(A.DELAY_MTS,0),
  TRUNCATE(A.DELAY_MTS/60,0),
  TRUNCATE(A.DELAY_MTS/60,5),
  TRUNCATE(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.

 
	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;
 

 
	DROP PROCEDURE IF EXISTS EMP_MGMT_REMOVE_EMP;
//
CREATE PROCEDURE EMP_MGMT_REMOVE_EMP(employee_id INT)
BEGIN
   DELETE FROM employees
   WHERE employees.employee_id = remove_emp.employee_id;
END;
//
 

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.

 
	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 ;
 
 

 
	CREATE PROCEDURE     PROC_A_T2()
BEGIN
 
 
 
 
   DECLARE V1 INT;
   DECLARE V2 INT;
 
 
   DECLARE SWV_NO_DATA INT DEFAULT 0;
   DECLARE SWV_CUR1_COL1 TEXT;
   DECLARE SWV_CUR1_TOTAL BIGINT;
   DECLARE CUR1 CURSOR FOR SELECT  CASE WHEN UPPER(COL2) IN('Q') THEN CONCAT(COL1,'Request')
   WHEN UPPER(COL2) IN('R') THEN CONCAT(COL1,'Response')
   WHEN UPPER(COL2) IN('*') THEN CONCAT(COL1,'Unknown')
   ELSE COL1 END COL1, CNT TOTAL
   FROM(SELECT
      COL1, COL2,  COUNT(*) CNT
      FROM A_T2
      GROUP BY COL1,COL2) AS TABAL
   GROUP BY COL1,COL2,CNT
   ORDER BY ISNULL(COL1),COL1;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET SWV_NO_DATA = -1;
 
 
 
 
   SET V1 = 1;
   IF V1 > 0 THEN
      OPEN CUR1;
      SET SWV_NO_DATA = 0;
      FETCH CUR1 INTO SWV_CUR1_COL1,SWV_CUR1_TOTAL;
      WHILE SWV_NO_DATA = 0 DO
         SET V2 = ROUND((SWV_CUR1_TOTAL/V1)*100,4);
         SET SWV_NO_DATA = 0;
         FETCH CUR1 INTO SWV_CUR1_COL1,SWV_CUR1_TOTAL;
      END WHILE;
      SET SWV_NO_DATA = 0;
      CLOSE CUR1;
   END IF;
END;
 

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.

 
	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.