Friday, September 28, 2007

Oracle XE and PHP 5 on Windows

Even though most PHP applications use MySQL / SQLite / Postgres for database driven applications sometimes you need to integrate information from an Oracle database and you need PHP to do the talking. Oracle XE helped and encouraged developers to write applications using Oracle. So, there are several ways to connect to an Oracle:
  • using odbc
  • oci8 extension available for both PHP 4 and PHP 5
  • PDO (pdo_oci)
I will present some basic configuration for accesing Oracle [XE] database using OCI extension and PDO. PHP using OCI8 to access Oracle XE database First of all make sure the server (Oracle instance) is running and you have a valid user/password combination. Also make sure, the user is not locked. In php.ini uncomment:
Note: make sure extension_path is set correctly. php_oci8.dll depends on oci.dll. You can find oci.dll in oracle bin folder (something like \oraclexe\app\oracle\product\10.2.0\server\BIN). Copy oci.dll in %WINDIR%\system32. Now restart apache web server. Sample connection script:
$ora_conn =  oci_connect('hr','****','xe');

$statement = oci_parse ($ora_conn, $sql);
oci_execute ($statement);
while ($row = oci_fetch_assoc($statement)) {
And that's about it. PHP using PDO to access Oracle XE database Uncomment in php.ini the following lines:
Also, see above note referring to oci.dll. Restart Apache web server. Sample script using PDO:

try {
    $dbh = new PDO('oci:host=localhost;dbname=xe', 'hr', '*****');
    $rs = $dbh->query('SELECT * from EMPLOYEES WHERE ROWNUM<10');
    if($rs) {
        foreach ($rs as $row) {

    $dbh = null;
} catch (PDOException $e) {
    print 'Error!: ' . $e->getMessage();