To convert a PHP application that uses Oracle (personal edition or otherwise) to MySQL, you’ll need to migrate both the database and the PHP code that interacts with it. Here's a clear roadmap to handle the conversion:
✅ Step-by-Step Oracle to MySQL Migration (for PHP Applications)
🔁 1. Migrate the Oracle Database to MySQL
a. Export Oracle Schema and Data
You can extract Oracle data using:
-
SQL Developer(via Export wizard: CSV/SQL) -
exporexpdptools (for advanced use)
b. Create Equivalent MySQL Tables
Manually or using tools like:
-
MySQL Workbench
-
Oracle2MySQL Converter (GUI tools like
DBConvert) -
Custom scripts to map Oracle datatypes to MySQL
📌 Datatype Mapping Examples:
| Oracle | MySQL |
|---|---|
NUMBER |
INT, FLOAT, or DECIMAL |
VARCHAR2(50) |
VARCHAR(50) |
CLOB |
TEXT |
DATE |
DATETIME or DATE |
c. Import Data to MySQL
If you exported data as CSV:
LOAD DATA INFILE 'data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
🔧 2. Update PHP Code
a. Change the Database Connection
If your old code uses:
$conn = oci_connect('username', 'password', 'localhost/XE');
Change to PDO MySQL:
$pdo = new PDO("mysql:host=localhost;dbname=your_db", "username", "password");
b. Modify Query Syntax
Oracle and MySQL differ slightly in SQL syntax.
Examples to modify:
| Oracle SQL | MySQL Equivalent |
|---|---|
SYSDATE |
NOW() |
ROWNUM |
LIMIT |
NVL(col, 'N/A') |
IFNULL(col, 'N/A') |
TO_CHAR(DATE, 'YYYY-MM-DD') |
DATE_FORMAT(DATE, '%Y-%m-%d') |
c. Update Bind Parameters
Oracle OCI:
oci_bind_by_name($stmt, ":id", $id);
MySQL PDO:
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);
d. Change Fetch Methods
Oracle:
$row = oci_fetch_assoc($stmt);
MySQL PDO:
$row = $stmt->fetch(PDO::FETCH_ASSOC);
✅ 3. Test the Application
-
Test each CRUD operation (Create, Read, Update, Delete)
-
Check for SQL errors due to syntax differences
-
Validate data formats (especially
DATE,BOOLEAN, andENUM)
🛠️ Optional: Tools That Help
-
DBConvert for Oracle to MySQL – GUI-based
-
SQL Workbench/J – for scripting migration
-
HeidiSQL or DBeaver – general DB management
Would you like help converting a specific PHP script or SQL query from Oracle to MySQL? Feel free to paste it.
