PHP: Migrating from MySQL to Oracle

How to switch from MySQL to Oracle? This tutorial will explain you some useful tips. Let say that you want to migrate all your data from MySQL database to Oracle database. In MySQL database you have a usernames table. Using phpMyAdmin you can export "create table usernames" statement and copy it to notepad. It looks like that:

CREATE TABLE IF NOT EXISTS `usernames` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`lastname` varchar(15) NOT NULL DEFAULT '',
`firstname` varchar(15) NOT NULL DEFAULT '',
`username` varchar(20) NOT NULL DEFAULT '',
`password` varchar(50) NOT NULL DEFAULT '',
`email` varchar(100) NOT NULL DEFAULT '',
`role` varchar(20) NOT NULL DEFAULT '',
`dob` datetime DEFAULT NULL,
`address` varchar(50) NOT NULL DEFAULT '',
`city` varchar(20) NOT NULL DEFAULT '',
`state` varchar(2) NOT NULL DEFAULT '',
`zip` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Now, you have to recreate the same table in Oracle. You may use Oracle SQL Plus. The syntax to create table statement in Oracle database is little bit different than in MySQL. First, Oracle does not have AUTO_INCREMENT attribute. MySQL will add one auto generated unique number to this auto incremented field. In Oracle we will define this field as a number. A new create table statement for Oracle database will look like that:

CREATE TABLE usernames(
userid NUMBER(11),
lastname VARCHAR2(20),
firstname VARCHAR2(20),
username VARCHAR2(20),
password VARCHAR2(50) NOT NULL,
email VARCHAR2(100) NOT NULL,
role VARCHAR2(20) NOT NULL,
dob DATE,
address VARCHAR2(50),
city VARCHAR2(20),
state VARCHAR2(2),
zip VARCHAR2(10),
CONSTRAINT pk_emp PRIMARY KEY (userid)
);

Execute the "create table usernames" statement in Oracle SQL Plus and then execute commit command:
commit;
Now, to add auto increment to userid field in the Oracle table you have to create sequence:

create sequence usernames_seq
start with 1
increment by 1
nomaxvalue;

Execute the create sequence statement in Oracle SQL Plus and then execute the commit command:
commit;
You may create a trigger:

create trigger usernames_trigger
before insert on usernames
for each row
begin
select usernames_seq.nextval into :new.userid from dual;
end;
/

Now, in your insert query you may use usernames_seq.nextval in place of userid value and it will be auto incremented.

insert into usernames values(usernames_seq.nextval, 'Smith', 'James','secret','jamess@yahoo.com','admin','29-JAN-01','12 West 15 Street','Dunedin','FL','34697');

You can view you sequences and triggers by executing the following query in SQL Plus:

select sequence_name from user_sequences;
select trigger_name from user_triggers;

You can disable or enable your triggers by executing the following query in SQL Plus:

alter trigger usernames_trigger disable;
alter trigger usernsmes_trigger enable;

One more thing you have to fix in your insert query is the date format. In MySQL date is saved as '2001-01-29' in Oracle it is saved as '29-JAN-01'
Compare MySQL insert statement and Oracle insert statement:
MySQL:

insert into usernames values(0, 'Smith', 'James','secret','jamess@yahoo.com','admin','2001-01-29','12 west 15 STREET','Dunedin','FL','34697');

Oracle:

insert into usernames values(usernames_seq.nextval, 'Smith', 'James','secret','jamess@yahoo.com','admin','29-JAN-01','12 west 15 STREET','Dunedin','FL','34697');

In PHP code you can use strtotime function to convert time from MySQL format to Oracle format:

$dob = date('d-M-y', strtotime($dob));

Or you can leave MySQL date format as it is, but then you have to use in your insert statement TO_DATE(<string>, '<format>') function:

insert into usernames values(0, 'Smith', 'James','secret','jamess@yahoo.com','admin',
to_date('2001-01-29:12:00:00AM', 'yyyy-mm-dd:hh:mi:ssam'),'12 west 15 STREET','Dunedin','FL','34697');

If you want to insert your records using SQL Plus then you can change session by using the following command:

alter session set nls_date_format='YYYY-MM-DD';

Commit the changes and use insert statement from MySQL as it is.

insert into usernames values(0, 'Smith', 'James','secret','jamess@yahoo.com','admin','2001-01-29','12 west 15 STREET','Dunedin','FL','34697');

Connection to Oracle database

To connect to MySQL database we use the following parameters:

$hostname = "localhost";
$dbuser = "myuser";
$dbpassword = "secret";
$dbname = "mydb";
$dblink=mysql_connect( $hostname, $dbuser, $dbpassword)
or die ( 'Unable to connect to server. (login)' );

// Select database on MySQL server
mysql_select_db($dbname, $dblink)
or die ( 'Unable to select database.(login)' );

To connect to Oracle database we use the following PHP code:

$conn=oci_connect($dbuser, $dbpassword, $dbhost);

Where $dbuser is a user, $dbpassword is a password and $dbhost is a connection string.
The Easy Connect string for Oracle 10g is of the form: [//]host_name[:port][/service_name]. With Oracle 11g, the syntax is: [//]host_name[:port][/service_name][:server_type][/instance_name]. Service names can be found by running the Oracle utility lsnrctl status on the database server machine.
http://php.net web site is a good reference for oracle functions.
Connection string example:

$dbhost="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myOracleDB)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=myOracleDB.mydomain.com)(SERVER=DEDICATED)))";

Check if connection is created!

if (!$conn)
{
$e = oci_error(); // For oci_connect errors pass no handle
echo "if not connection<br>";
echo htmlentities($e['message']);
}
else
{ //Select all users from usernames
$sql="select * from usernames order by username";
$stmt = oci_parse($conn, $sql);

oci_execute($stmt, OCI_DEFAULT);
while ($row = oci_fetch_array($stmt, OCI_BOTH)) {
$userid=$row[0];
$lastname=$row[1];
$firstname=$row[2];
$username=$row[3];
$password=$row[4];
$email=$row[5];
$role=$row[6];
$dob=$row[7];
$address=$row[8];
$city=$row[9];
$state=$row[10];
$zip=$row[11];
}
if($stmt)
oci_free_statement($stmt);
if($conn)
oci_close($conn);

} When you use OCI_BOTH oci_fetch_array() mode, you can use column name as the output array index: $username=$row['username'];