Categories
PHP

Using Oracle

This tutorial covers the essentials you need to know to use Oracle with PHP.

Prerequisites:

  • The environment variable ORACLE_HOME must be set so that PHP can find the client libraries.
  • Windows users need the php.ini directive extension=php_oci8***.dll.
  • PHP requires read access to the client libraries.

The oci_connect() tries to establish a connection to the server. The order of the parameters is first username and password, then the name of the service (that has been configured using the configuration assistant or is part of the tnsnames.ora file).

Connecting to Oracle:

<?php
 $conn = oci_connect('username', 'password', '//localhost/db_service');
 if (!$conn) {
  $error = oci_error();
  echo $error['code'] . '<br>';
  echo $error['message'];
  exit;
 }
 echo 'Connected';
 oci_close($conn);

Sending SQL to Oracle

<?php
 $conn = oci_connect('username', 'password', '//localhost/db_service');
 if (!$conn) {
  $error = oci_error();
  echo $error['code'] . '<br>';
  echo $error['message'];
  exit;
 }

 $stmt = oci_parse($conn, 'INSERT INTO user_table
            (name, email) VALUES (:name, :email)');
 oci_bind_by_name($stmt, ':name', $_POST['name']);
 oci_bind_by_name($stmt, ':email', $_POST['email']);
 oci_execute($stmt);

 echo 'Record saved.';
 oci_close($conn);

To send SQL to Oracle, two steps are required. First, a call to oci_parse() parses an SQL string and returns a resource that can then be executed using oci_execute().

The preceding code uses the prepared statements to escape the dangerous characters in the SQL string, prepared statements are a must but are also very easy to implement. For this, the SQL statement must contain placeholders that start with a colon:

<?php
 //...
 $stmt = oci_parse($conn, 'INSERT INTO user_table
            (name, email) VALUES (:name, :email)');

Then, these placeholders have to be filled with values. For this, oci_bind_by_name() must be used:

<?php
 //...
 oci_bind_by_name($stmt, ':name', $_POST['name']);

The preceding code sends some form data to the database. No worries about special characters because oci_bind_by_name takes care of that.

Retrieving Results of a Query to Oracle

You have several ways to access the return values of an SQL query, but the following functions are used most often in practice:

  • oci_fetch_assoc() returns the current row in the resultset as an associative array.
  • oci_fetch_object() returns the current row in the resultset as an object.
  • oci_fetch_row() returns the current row in the resultset as a numeric array.
  • oci_fetch_all() returns the complete resultset as an array of associative arrays. However, five parameters are required: the statement object from oci_parse(), the array that is used for the return data, the number of lines to skip, the maximum number of rows to be returned (-1 means infinite), and whether to return a numeric (OCI_NUM) or associative (OCI_ASSOC) array. See https://php.net/manual/function.oci-fetch-all.php.

The listing in This uses a while loop and oci_fetch_object() to retrieve all data in the table.

Retrieving Data from Oracle

<?php
 $conn = oci_connect('username', 'password', '//localhost/db_service');
 if (!$conn) {
  $error = oci_error();
  echo $error['code'] . '<br>';
  echo $error['message'];
  exit;
 }
 
 $stmt = oci_parse($conn, 'SELECT * FROM quotes');
 oci_execute($stmt);
 while ($row = oci_fetch_object($stmt)) {
  echo $row->ID.', '.$row->NAME.', '.$row->EMAIL.'<br>';
 }
 oci_close($db);

Oracle always returns column names in uppercase. Therefore, you have to use uppercase object properties or uppercase associative array keys when accessing the return values of an SQL query.

Working with Databases: