Goal: Migrate data in an Oracle database to Drupal nodes.
Steps:
Create a backup of your site. Chances are good something will go wrong.
Install Oracle Database 11g Express Edition from http://www.oracle.com/technetwork/products/express-edition/downloads/index.html
Install Oracle SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
Download and extract the Oracle Instant Client from http://www.oracle.com/technetwork/database/features/instant-client/index-100365.html Then add the location of the extracted folder to the Windows's path. (The Windows Path is accessed by Start Menu, right-click Computer and choose Properties, Advanced system settings, Environment Variables)
Enable php_oci8_11g.dll extension from the IIS Manager. Note that only one of the oci8 extensions can be enabled at a time, so make sure that the php_oci8.dll extension is disabled.
Verify OCI8 extension enabled correctly by checking phpinfo() from the IIS Manager located in the PHP Manager subsection.
Install the migrate module in Drupal from http://drupal.org/project/migrate
Add the following code to the settings.php file for the site. I put it just after the $databases section.
$conf['oracle_db'] = array(
'username' => 'system',
'password' => 'password',
'connection_string' => '//127.0.0.1/xe',
);Enable the Migrate, Migrate UI, and Migrate example-Oracle modules. If there is a problem with Oracle or the OCI8 PHP extension, this is when you will see the errors. If everything is OK then a new table called ORACLE_CONTENT will be created in Oracle. If you look at it with the Oracle SQL Developer you will see some fields for Title, Body, and Main Image.
In Drupal navigate to admin/content/migrate. There should be a listing for MigrationExampleOracleNode. Check the box next to it and click 'Execute'. If everything works you will have a new content type of 'migration_example_oracle' and four nodes of that type. What? It didn't just work for you? That's fine. I had to edit the file migrate_example_oracle.migrate.inc and delete all the code associated with image import.
If the import didn't work go back to admin/content/migrate and change the operations drop down select to rollback and then click execute. Now fix the file migrate_example_oracle.migrate.inc and repeat.
Repeat import, rollback and edit until your nodes import the way you want them to.
Testing php_oci8_11g.dll extension
After I successfully enabled the Migrate example-Oracle module and verified that the table was created in Oracle I still had trouble using migrate. I wanted to verify that the Oracle extension was properly working so I created a stand alone php file outside of Drupal. To test if the Oracle extension was working I create a file called phpoci.php and placed it in my root web folder. I then visited http://localhost/phpoci.php to check if I was reading the Oracle data through PHP correctly.
<?php
//Copy this code and save it as phpoci.php in your root web folder.
//Change the $conf settings to match your Oracle installation.
//This will look for the table ORACLE_CONTENT that was created by the Migrate example-Oracle module.
//If this runs successfully the webpage will display a table with Title and Body content.
global $conf;
$conf['oracle_db'] = array(
'username' => 'system',
'password' => 'password',
'connection_string' => '//127.0.0.1/xe',
);
// Should never fail - we can't get here unless hook_requirements passed, right?
$connection = @oci_connect($conf['oracle_db']['username'], $conf['oracle_db']['password'],
$conf['oracle_db']['connection_string'], 'UTF8');
if (!$connection) {
$e = oci_error();
throw new Exception($e['message']);
}
// Create a table to hold test data
$query = "SELECT TITLE, BODY FROM ORACLE_CONTENT";
$result = oci_parse($connection, $query);
// if (!$result) {
// $e = oci_error($connection);
// throw new Exception($e['message'] . "\n" . $e['sqltext']);
// }
$status = oci_execute($result);
// if (!$status) {
// $e = oci_error($result);
// throw new Exception($e['message'] . "\n" . $e['sqltext']);
// }
$nrows = oci_fetch_all($result, $status);
echo "<html><head><title>Oracle PHP Test</title></head><body>";
echo "<center><h2>Oracle PHP Test</h2><br>";
echo "<table border=1 cellspacing='0' width='50%'>\n<tr>\n";
echo "<td><b>Title</b></td>\n<td><b>Body</b></td>\n</tr>\n";
for ($i = 0; $i < $nrows; $i++ ) {
echo "<tr>\n";
echo "<td>" . $status["TITLE"][$i] . "</td>";
echo "<td>$ " . $status["BODY"][$i] . "</td>";
echo "</tr>\n";
}
echo "<tr><td colspan='2'> Number of Rows: $nrows</td></tr></table>";
echo "<br><em>If you see data, then it works!</em><br></center></body></html>\n";
print $query;
print "<br />";
print $result;
print "<br />";
print $status;