Oracle 10g XE and PHP

Share this article

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

How do I connect Oracle 10g XE with PHP?

To connect Oracle 10g XE with PHP, you need to use the OCI8 extension. First, ensure that the Oracle Instant Client is installed on your system. Then, in your PHP script, use the oci_connect() function to establish a connection. The function takes three parameters: username, password, and connection string. The connection string should be the TNS name of your Oracle database.

What is Oracle 10g XE?

Oracle 10g XE (Express Edition) is a free, lightweight, and feature-limited edition of Oracle’s 10g database. It’s designed for developers, small businesses, and students. Despite its limitations, it includes most of the functionality of the full Oracle database, including PL/SQL, the Oracle Call Interface, Java support, and XML DB.

How do I install the OCI8 extension in PHP?

To install the OCI8 extension, you need to download and install the Oracle Instant Client first. Then, you can use the PECL command to install the OCI8 extension. After the installation, add the extension to your php.ini file and restart your web server.

What are the limitations of Oracle 10g XE?

Oracle 10g XE has several limitations compared to the full Oracle database. It can use up to 1 GB of RAM, store up to 4 GB of user data, and use one CPU on the host machine. Despite these limitations, it’s a fully functional Oracle database for small-scale applications.

How do I execute SQL queries in PHP?

To execute SQL queries in PHP, you can use the oci_parse() and oci_execute() functions. The oci_parse() function prepares an Oracle statement for execution, and the oci_execute() function executes the statement. After executing a SELECT statement, you can use the oci_fetch_array() function to fetch the results.

How do I handle errors in Oracle 10g XE and PHP?

Oracle errors can be handled in PHP using the oci_error() function. This function returns an associative array that contains detailed information about the last error that occurred on the connection.

Can I use Oracle 10g XE for production?

Yes, you can use Oracle 10g XE for production, but you need to be aware of its limitations. If your application requires more resources than Oracle 10g XE can provide, you should consider upgrading to a full Oracle database.

How do I install Oracle 10g XE?

Oracle 10g XE can be downloaded from the Oracle website. The installation process is straightforward and involves running the installer and following the on-screen instructions.

How do I manage Oracle 10g XE?

Oracle 10g XE includes a web-based management interface that you can use to manage your database. You can access this interface by navigating to http://localhost:8080/apex in your web browser.

How do I upgrade from Oracle 10g XE to a full Oracle database?

To upgrade from Oracle 10g XE to a full Oracle database, you need to export your data from Oracle 10g XE, install the full Oracle database, and then import your data into the new database. Oracle provides tools and documentation to assist with this process.

Harry FuecksHarry Fuecks
View Author

Harry Fuecks is the Engineering Project Lead at Tamedia and formerly the Head of Engineering at Squirro. He is a data-driven facilitator, leader, coach and specializes in line management, hiring software engineers, analytics, mobile, and marketing. Harry also enjoys writing and you can read his articles on SitePoint and Medium.

Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week