Connecting to an Oracle database

Introduction

This section explains how to connect to and query with PL/SQL (Oracle's SQL) Oracle 7 and 8i databases.

The functions we use to connect to an Oracle database with PHP have been written with the Oracle's OCI8 library (Oracle8 Call-Interface). This is why all these functions' name start with "OCI".

Connecting to the server

To do so, we use the function OCILogOn:

This command line will connect the Web server to the Oracle schema $db. $db is a connection string defined in the file tnsnames.ora. For example, $db will be set to "MY_DATABASE" if tnsnames.ora contains a sequence similar to this one:

MY_DATABASE =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = my.oracle.server.com)
      (PORT = 1521)
    )
    (CONNECT_DATA = (SID = ORACLE)
    )
  )

Querying with PL/SQL

There are two steps: we first prepare the query, and then we execute it to retrieve a result.

We prepare queries with the function OCIParse:

OCIParse has two arguments: a connection identifier (the result of OCILogOn) and the query. The returned value of this step will be next used to execute the query and retrieve a result. It is possible to combine two OCIParse with the same connection ($connect) and get the 2 results in 2 variables $stmt1 and $stmt2 in order to execute both queries at the same time to the same database.

We execute a query with OCIExecute:

This function has one argument: the result of OCIParse.

Calls to stored procedures and PL/SQL commands can be performed the same way:

$stmt = OCIParse($connect, "BEGIN PROCEDURE_STOCKEE(:arg1, :arg2); END;");
OCIBindByName($stmt, ":arg1", &$arg1, -1);
OCIBindByName($stmt, ":arg2", &$arg2, -1);
OCIExecute($stmt);

We will see later what the function OCIBindByName is used for.

An ordinary PL/SQL query (SELECT, UPDATE, INSERT...) does not end with a semi-colon (";"), though a call to a stored procedure ends with a semi-colon (";").

How to get and use the result of a query

We need to use jointly 2 functions: OCIFetch and OCIResult. OCIFetch returns a row from the result, although OCIResult returns a column value from a row we've just got with OCIFetch. In the following example, we are going to query the table articles that has 2 columns: article and price:

$stmt = OCIParse($connect, "SELECT * FROM articles WHERE price<=100");
OCIExecute($stmt);
$counter = 0;
while (OCIFetch($stmt)) {
	$counter++;
	echo OCIResult($stmt, "ARTICLE")." costs $";
	echo OCIResult($stmt, "PRICE")."<BR>\n";
}
OCIFreeStatement($stmt);
echo "$counter articles found.";

Like OCIExecute, both functions use the variable $stmt returned by OCIParse.

Warning! The name of columns we use with OCIResult must be written in capitals.

Even if the query returns only one single row (this happens with a "SELECT COUNT(*)", for instance), it is necessary to use OCIFetch before getting and exploiting any value. Example:

$query = "SELECT COUNT(*) total FROM articles WHERE price<=100";
$stmt = OCIParse($connect, $query);
OCIExecute($stmt);
OCIFetch($stmt);
echo OCIResult($stmt, "TOTAL")." articles cost less than $100.";
OCIFreeStatement($stmt);

We can notice we used a variable $counter in the previous example. This was done deliberately. Indeed, contrary to MySQL and the function mysql_num_rows, it is not possible to know directly the number of rows fetched by a SELECT query. Yet, there is function called OCIRowCount, but it returns only a number of affected rows. It cannot but be used with an UPDATE or INSERT query.

Stored procedure arameters and long variables

PHP is commonly used to build dynamique SQL queries by directly inserting values into a query string:

$query = "SELECT * FROM staff WHERE name='$name'";
$stmt = OCIParse($connect, $query);
OCIExecute($stmt);
while (OCIFetch($stmt)) {
	...
}
OCIFreeStatement($stmt);

This is extremely convenient, but for very long variables (texts returned by forms, for example), we may meet with troubles: query strings cannot be more than 4.000 characters long. We solve this problem with the function OCIBindByName:

$stmt = OCIParse($connect, "INSERT INTO memos (text) VALUES (:text)");
OCIBindByName($stmt, ":text", &$form_text, -1);
OCIExecute($stmt);
OCIFreeStatement($stmt);

OCIBindByName is used to associate the variable $form_text with the identifier :text used in the query string (it is important to use ":" in the query string). $form_text must be passed as a reference to the function. This is why we use &$form_text. The last argument OCIBindByName has is the maximum length :text can have. If we use -1, PHP will use the current length of $form_text.

This function must be called before OCIExecute and after OCIParse.

We also use this function to pass arguments to stored procedures, as we saw it earlier.

Finishing a query

Once we have finished exploiting the result of a query, it is better to free resources that PHP allocated by using OCIFreeStatement:

$stmt = OCIParse($connect, "SELECT * FROM table");
OCIExecute($stmt);
...
OCIFreeStatement($stmt);

// We close the connection to the Oracle server
OCILogOff($connect);

We close the connection to the Oracle database with the function OCILogOff.

Useful functions

Reference

The OCI8 section of php.net

 printable format