Convert Oracle Tables to MySQL
RazorSQL includes a database conversion tool that can convert one or many Oracle tables to MySQL. The conversion tool can be
accessed via the following menu option in RazorSQL:
DB Tools -> Database Conversion
Inside the Database Conversion menu there are options for converting one table or for converting a selection of tables within
a specific schema.
Selecting the Single Table option brings up the following screen:
Information needed on the screen includes the existing Oracle table to convert, the name of the new MySQL table to create, and whether to execute the conversion directly on a MySQL database connection or whether to generate an SQL script file with the DDL and SQL insert statements for the conversion.
After clicking the convert button, RazorSQL will generate a MySQL specific create table statement for the table. The create table statement will include the column data type translations from any Oracle specific types to their MySQL equivalents so that the create table statement can execute on MySQL databases. It will also generate an SQL insert statement for each row in the Oracle table that can be executed against the new MySQL table. Listed below is an example SQL script that would be generated by the RazorSQL Oracle to MySQL conversion tool.
CREATE TABLE PROJECT (
PNUMBER DECIMAL NOT NULL,
PNAME VARCHAR(50),
PLOCATION VARCHAR(50),
DNUM DECIMAL,
PRIMARY KEY (PNUMBER)
);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (1, 'ProductX', 'Bellaire', 5);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (2, 'ProductY', 'Sugarland', 5);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (3, 'ProductZ', 'Houston', 5);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (10, 'Computerization', 'Stafford', 4);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (20, 'Reorganization', 'Houston', 1);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (30, 'Newbenefits', 'Stafford', 4);
When electing to convert multiple tables, in addition to the create table statement and SQL insert statements, RazorSQL can generate alter table statements to set up foreign key relationships. Listed below is an example Oracle to MySQL conversion script that includes the alter table statement to create the foreign key on the Project table that references the Department table.
CREATE TABLE DEPARTMENT (
DNUMBER DECIMAL NOT NULL,
DNAME VARCHAR(25) NOT NULL,
PRIMARY KEY (DNUMBER)
);
CREATE TABLE PROJECT (
PNUMBER DECIMAL NOT NULL,
PNAME VARCHAR(50),
PLOCATION VARCHAR(50),
DNUM DECIMAL,
PRIMARY KEY (PNUMBER)
);
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (5, 'Research');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (4, 'Administration');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (1, 'Headquarters');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (7, 'test2');
INSERT INTO DEPARTMENT(DNUMBER, DNAME) VALUES (8, 'test3');
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (1, 'ProductX', 'Bellaire', 5);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (2, 'ProductY', 'Sugarland', 5);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (3, 'ProductZ', 'Houston', 5);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (10, 'Computerization', 'Stafford', 4);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (20, 'Reorganization', 'Houston', 1);
INSERT INTO PROJECT(PNUMBER, PNAME, PLOCATION, DNUM) VALUES (30, 'Newbenefits', 'Stafford', 4);
ALTER TABLE PROJECT
ADD FOREIGN KEY (DNUM)
REFERENCES DEPARTMENT (DNUMBER);
