Oracle 10g XE and PHP

In case you missed it, yesterday Oracle announced a free (as in beer) version of their database – Oracle 10g Express Edition (XE) – basically a “lite” version – some industry analysis here. Significance of this move aside, more interesting is having a play. Managed to get the equivalent of a “Hello World” from PHP to Oracle up in under 1.5 hours today (ran into a specific glitch that required a re-install otherwise would have been less time). Here’s how…

Download is available at here. Be warned you’ve got a number of links to follow, an online license form to agree (check all the boxes) and finally a registration to fill in, before you get to the actual download. The download itself is about 150Mb and your system will need at least 250Mb RAM available to actual run the DB.

In my case installed it under Windows XP. There’s also a Linux version which I’ll be trying under Ubuntu later this week (had some hard disk issues recently which meant reinstall everything). Nothing available for OSX yet (the standard Oracle DB does support OSX though, so expect it’s a matter of time).

Once you’ve downloaded it, run the installer. It will ask you for a password for the “systen” user (think “root” user) which you should make sure you remember. The rest of the installation is just a matter of clicking “Next”. When finished it fires up a browser so you can login to your local instance of HTMLDb – something roughly equivalent to phpMyAdmin. In general, if you’ve ever tried installing the full Oracle database, this installation is breeze by comparison (not even a Java based installer – InstallShield I believe but could be wrong).

To login, you need the username “system” and the password you entered in the install process. You then need to following the instructions provided in the “Getting Started” guide (the installer adds a link to the guide on your start menu), in particular how to enable the “HR” user.

With that done, the next step is PHP. First things first you need to create a couple of environment variables (Control Panel > System > Advanced > Environment Variables );

• ORACLE_SID = XE (…think of this as the name of your database)
• ORACLE_HOME = C:oraclexeapporacleproduct10.2.0server (…assuming a default installation path)

If you’re running Apache as a Windows service, the easiest thing to do now is reboot Windows.

Now edit your php.ini and uncomment extension=php_oci8.dll (I assume here you have extension_dir pointing at the right place) – note I’m running PHP 5.0.5 and the code below reflects that. Restart Apache and check out your phpinfo(). If you don’t get error messages about being unable to find php_oci8.dll, all is probably good. If you do get that error message, either the dll really can’t be found (check it exists in your extension_dir) or the above two environment variables are incorrectly set (or Apache is running as a service and you haven’t yet rebooted Windows).

Assuming all went well and you can see the OCI8 extension listed by phpinfo(), here’s something like a “Hello World”…

<?php
$conn = oci_connect('hr','hr'); // This assumes you followed the Getting Start guide...

$sql = "SELECT * FROM employees";

$stmt = oci_parse($conn, $sql);

oci_execute($stmt);

echo '<pre>';

while ( $row = oci_fetch_assoc($stmt) ) {
  print_r($row);
}

echo '</pre>';
?>

Oracle has a bunch more info for PHP developers here.

From reading around and the little playing I’ve done, apart from the resource restrictions (one db per system, 1GB or mem usage max, 4GB database size max), it seems to have all the functionality as a normal Oracle DB (would be interested to hear more info on that if anyone has some).

Note that glitch I encounted, which may help non-US users, was I first attempted installing with Windows set to Swiss German as preferred language, which led to an ORA-27101 when I tried to login with SQL*Plus (and HTMLDb wouldn’t start). Switching to US English (Control Panel > Regional Settings > make everything “US English” on all tabs) solved this after a re-install.

Anyway – overall a pleasant installation experience. Oracle have definately “worked it out” in making getting started pain-free. Whether this will result in a big uptake amongst those that might otherwise choose an Open Source DB is hard to say. With the resource restrictions placed on the use of Oracle XE I doubt it’s really an option for shared hosts but it could be useful for software vendors who need to distribute a DB with their products. For developers it’s certainly a good place to start getting to know Oracle.

Frequently Asked Questions about Oracle 10g XE and PHP