DatabaseCombining an Oracle Database with PHP to Manage Data

Combining an Oracle Database with PHP to Manage Data

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

PHP is the one of the most commonly used scripting languages. The Oracle database is the most commonly used commercial database (according to Gartner, Inc.). PHP and the Oracle database make a great combination suitable for developing web applications. The PHP Oracle extension may be used to connect to Oracle database 10g, 9i, and 8i by using the Oracle Call Interface (OCI). The PHP extension for the Oracle database is included in the PHP 5 installation. The Oracle PHP extension provides features such as connection caching (pooling), privileged connections using external credentials, statement caching, and row pre-fetching. In this article, you will install PHP’s OCI extension for Oracle database, decide which connection method to use, create a database, and retrieve data from that database and display it in a table.

Installing the Oracle Extension

First, install PHP 5 and Apache HTTP Server 2. Add C:/PHP, the directory in which PHP 5 is installed, to the PATH environment variable. Rename the php.ini-recommended.configuration file in the C:/PHP directory to php.ini. The example application in this article will store and fetch data from Oracle database 10g. Therefore, enable the Oracle database extension in php.ini configuration file by removing the ‘;’ from the following line.

extension=php_oci8.dll

Set the extension directory directive in php.ini to the directory containing the PHP extensions.

extension_dir = "./ext"

Next, configure Apache HTTP Server 2 with PHP 5 by adding the following configuration directives to <Apache2>/conf/httpd.conf file; <Apache2> being the directory in which Apache 2 web server is installed.

# For PHP 5
LoadModule php5_module "C:/PHP/php5apache2.dll"
AddType application/x-httpd-php .php

# configure the path to php.ini
PHPIniDir "C:/PHP/"

You need to restart the Apache 2 server. Install the Oracle database 10g, including the sample schemas. The OCI8 extension provides various configuration directives for the php.ini file. Some of the commonly used configuration directives are discussed in Table 1.

Table 1: OCI8 Configuration Directives

Directive Description Default Value
oci8.max_persistent Specifies the maximum number of persistent OCI8 connections per process.

-1

The default value specifies no limit.

oci8.persistent_timeout Specifies the maximum length of time (in seconds) that a process may maintain an idle persistent connection.

-1.

The default value indicates that the idle persistent connections do not timeout.

oci8.ping_interval Specifies the length of time (in seconds) between pings for persistent connections. If set to 0, persistent connections are pinged every time they are reused. Setting the value to -1 disables pings; this will make oci_pconnect() operate at the highest frequency, but does not detect faulty connections. 60
oci8.statement_cache_size Specifies number of statements to cache. Statement caching improves performance, but memory load also increases. Setting the value to 0 disables statement caching. 20
oci8.default_prefetch Specifies the default number of rows that will be pre-fetched after a statement is run; this improves performance, but memory load also increases. 10
oci8.privileged_connect Enables privileged connections using external credentials.

“0”

By default, privileged connections are not enabled.

If any of the configuration directives is modified, restart the Apache2 server. The Oracle OCI8 PHP class library provides various functions to connect to the Oracle database, run SQL statements, and retrieve result sets. Some of the commonly used OCI8 PHP 5 functions are discussed in Table 2. All the OCI8 PHP functions return FALSE on error.

Table 2: OCI8 Extension Functions

Method Description
oci_connect ( string username, string password [, string db [, string charset [, int session_mode]]] ) Connects to the Oracle server. The db parameter specifies the Oracle instance or the name of the entry in the tnsnames.ora. If db is not specified, the ORACLE_SID environment variable value is used. The charset parameter may be specified if the Oracle server version is >9.2. The default value of session_mode is OCI_DEFAULT. For privileged connections using external credentials, specify the session_mode value as OCI_SYSOPER or OCI_SYSDBA. Privileged connections are disabled by default. To enable privileged connections, set PHP configuration directive oci8.privileged_connect to “1”.
oci_close ( resource connection ) Closes an Oracle connection.
oci_new_connect ( string username, string password [, string db [, string charset [, int session_mode]]] )

Establishes a new connection to the Oracle database. The oci_connect() method returns the same connection resource if invoked more than once with the same parameters. In contrast, oci_new_connect() method does not cache connections and returns a new connection.

The parameters are the same as the oci_connect() method.

oci_pconnect ( string username, string password [, string db [, string charset [, int session_mode]]] ) Creates a persistent connection with the Oracle database. Persistent connections are cached and reused between connection requests; this results in increased performance because connections are not created and closed frequently. Persistent connections may be tuned with configuration directives oci8.persistent_timeout, oci8.max_persistent, and oci8.ping_interval. The method parameters are the same as for oci_connect() and oci_new_connection().
oci_parse ( resource connection, string query ) Prepares a SQL statement for execution. Returns a statement resource that may be used with oci_bind_by_name function to bind PHP variables to placeholders. A prepared statement is run with the oci_execute () function.
oci_bind_by_name ( resource stmt, string ph_name, mixed &variable [, int maxlength [, int type]] ) Binds a PHP variable to a placeholder in a prepared statement. The maxlength parameter specifies the maximum length of the bind variable. If maxlength is set to -1, the current length of the variable is used to set maxlength. The type parameter is used bind abstract data types such as LOB/ROWID/BFILE.
oci_execute ( resource stmt [, int mode] ) Executes a previously parsed statement. The mode parameter specifies the mode of execution, the default value being OCI_COMMIT_ON_SUCCESS. To not commit statements on completion, set mode to OCI_DEFAULT. The OCI_DEFAULT mode creates transactions that are rolled back when a connection is closed or when a script has run. With the OCI_DEFAULT mode, commit a transaction with oci_commit() or rollback the transaction with oci_rollback(). Returns TRUE if the statement executes without error and FALSE if the statement does not execute.
oci_error ( [resource source] ) Returns the last error found for the specified resource. For a connection resource, the resource parameter should not be specified. Returns FALSE if no error is found. If an error is found, the error is returned as an array consisting of an error code and error message string.
oci_commit ( resource connection ) Commits all outstanding statements for the active transaction for the specified connection resource. Returns TRUE if the statements get committed and FALSE if an error occurs.
oci_rollback ( resource connection ) Rolls back outstanding statements for the active transaction on the specified connection resource. Returns TRUE if the statements get rolled back and FALSE if an error occurs.
oci_statement_type ( resource statement ) Returns the type of a statement, as one of the following values: SELECT, INSERT, UPDATE, CREATE, DELETE, DROP, ALTER, DECLARE, BEGIN, UNKNOWN. The statement parameter is a statement resource returned by oci_parse() method.
oci_set_prefetch ( resource statement [, int rows] ) Sets number of rows to be pre-fetched after successful statement execution. Default value is 1. Returns TRUE on success, and FALSE on error.
oci_fetch_all ( resource statement, array &output [, int skip [, int maxrows [, int flags]]] )

Fetches all the rows in a result data (for a SELECT statement) into an array. Null fields are set to PHP NULL value. Returns the number of rows fetched, or FALSE if an error occurs. The “skip” parameter specifies the initial number of rows to skip in fetching rows from result set; the default is to not skip any rows. The “maxrows” parameter specifies the maximum number of rows to fetch; the default is to fetch all the rows. The “flags” parameter may set to one of the following values:

OCI_FETCHSTATEMENT_BY_ROW,

OCI_FETCHSTATEMENT_BY_COLUMN, (the default)

OCI_NUM,

OCI_ASSOC.

oci_fetch ( resource statement )

Fetches the next row into the result-buffer.

Returns TRUE if the next row gets fetched, or FALSE if no next row or if an error occurs.

oci_fetch_row ( resource statement ) Fetches the next row from a result set into a numeric array (0 based). Returns FALSE if no more rows. Null fields are set to PHP NULL value.
oci_fetch_object ( resource statement ) Returns the next row in a result set as an object. The object attributes correspond to fields in the statement. Oracle returns all field names in upper case; therefore, all attributes in the row object are in uppercase. Returns FALSE if no more rows in the result set.
oci_fetch_array ( resource statement [, int mode] )

Returns the next row from the result set as a numeric array (0 based), an associative array or both. Returns FALSE if an error occurs or no more rows in the result set to fetch. Associative indices in the result array are uppercase. The mode parameter may be set to any combination of the following:

OCI_BOTH: Returns an array with both associative and numeric indices.

OCI_ASSOC: Returns anassociative array.

OCI_NUM: Returns a numberic array.

OCI_RETURN_NULLS: Creates empty elements for Null fields.

OCI_RETURN_LOBS: Returns the value of a LOB of a descriptor.

oci_fetch_assoc ( resource statement ) Returns the next row from the result set as an associative array. Because Oracle returns field names in upper case, associative indices are in upper case also. Returns FALSE if an error occurs or no more rows to fetch.
oci_num_fields ( resource statement ) Returns the number of result fields in the statement (for a SELECT statement). Returns FALSE on error.
oci_num_rows ( resource stmt ) Returns the number of rows affected by statement execution. For a SELECT statement, it returns the number of rows in the result set. Returns FALSE on error.
oci_field_is_null ( resource stmt, mixed field ) Checks whether field is null. The field parameter may be specified as a field index or field name in uppercase. Returns TRUE if field is null.
oci_field_name ( resource statement, int field ) Returns the field name in uppercase for specified field index ( 1 based).
oci_field_precision ( resource statement, int field ) Returns field precision for a specified field index ( 1 based).
oci_field_size ( resource stmt, mixed field ) Returns field size in bytes. The field may be specified as a field index (1 based) or field name (uppercase).
oci_field_type ( resource stmt, int field ) Returns the field type for a field specified as a field index (1 based).
oci_result ( resource statement, mixed field ) Returns data from the specified field in the current row returned by the oci_fetch() function. The field may be specified as a field index ( 1 based) or field name (uppercase). Returns FASLE on error.
oci_cancel ( resource stmt ) Invalidates a cursor freeing up the associated resources.
oci_free_statement ( resource statement ) Frees up statement resources. For a statement obtained with oci_parse().

Which Connection Method to Use

The OCI8 PHP class library provides the oci_connect(), oci_pconnect(), and oci_new_connection() methods to connect to the Oracle database. If a non-persistent connection is required that is cached, use the oci_connect() method. When the oci_connect() method is invoked with the same parameters more than once in the same script, the same connection resource is returned. Caching of connections improves connection efficiency. The cache used by oci_connect() is deallocated when a script has run or when the connection resource is closed. If transactional isolation between two sets of queries is required, use the oci_new_connection() method; this returns a new connection. A connection returned by oci_new_connection() is not cached. If a persistent connection that is cached across requests is required, use the oci_pconnect() method. A persistent connection does not get closed when the script has run or with the oci_close(resource connection) method. A persistent connection reduces the overhead of opening and closing connections, thus improving performance. However, if an application connects to the Oracle database using a different set of credentials for different web users, the number of connections in the persistent cache increases to the point that the performance of the Oracle server decreases due to the many idle connections. Tune the persistent connections by setting a limit on the maximum number of persistent connections in the cache with the oci8.max_persistent configuration directive, and reducing the number of idle persistent connections with the oci8.persistent_timeout configuration directive.

Creating a Database Table

In this section, you will create an Oracle database table. Create a PHP script, createOracleTable.php, in the C:/Program Files/Apache Group/Apache2/htdocs directory, the document root directory of Apache web server. Define variables $username, $password, and $db for the Oracle database username, password, and database. Specify the $db variable value as the database SID value in the <Oracle10g>/NETWORK/ADMIN/tnsnames.ora file.

$username='OE';
$password='pw';

$db='(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
   )
)';

If the HOST, PORT, or SERVICE_NAME are different than those specified in the PHP script, modify the settings in the script. Use the oci_connect() method to connect with the database.

$connection = oci_connect($username, $password, $db);

If an error occurs in obtaining a connection with the Oracle database, retrieve the error using the oci_error() function and output an error message.


if (!$connection) {
   $e = oci_error();
   echo htmlentities($e['message']);
}

Prepare an Oracle statement to create a database table using the oci_prepare() function.


$stmt = oci_parse($connection,
   "CREATE TABLE OE.Catalog(CatalogId VARCHAR(25) PRIMARY KEY,
   Journal VARCHAR(25), Publisher Varchar(25), Edition VARCHAR(25),
   Title Varchar(45), Author Varchar(25))");

Execute the SQL statement using the oci_execute() method.

$r = oci_execute($stmt);

If an error occurs in generating the table, retrieve the error using the oci_error() function and output an error message.


if (!$r) {
   $e = oci_error($stmt);
   echo htmlentities($e['message']);
}else{

   echo  " Created tablenn";
}

Prepare an SQL INSERT statement to add a row of data to the Catalog table using the oci_prepare() statement.


$sql = "INSERT INTO OE.Catalog VALUES('catalog1', 'Oracle Magazine',
    'Oracle Publishing', 'Nov-Dec 2004', 'Database Resource Manager',
    'Kimberly Floss')";
$stmt = oci_parse($connection, $sql);

Run the SQL statement using the oci_execute() function.

$r = oci_execute($stmt);

Similarly, add another row of data. The PHP script, createOracleTable.php, that creates an Oracle database table is presented in Listing 1.

Listing 1: createOracleTable.php

<?php
$username='OE';
$password='pw';

$db='(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
   )
)';

$connection = oci_connect($username, $password, $db);

if (!$connection) {
   $e = oci_error();
   echo htmlentities($e['message']);
}

$stmt = oci_parse($connection,
   "CREATE TABLE OE.Catalog(CatalogId VARCHAR(25) PRIMARY KEY,
   Journal VARCHAR(25), Publisher Varchar(25), Edition VARCHAR(25),
   Title Varchar(45), Author Varchar(25))");
if (!$stmt) {
   $e = oci_error($connection);
   echo htmlentities($e['message']);
}
$r = oci_execute($stmt);
if (!$r) {
   $e = oci_error($stmt);
   echo htmlentities($e['message']);
}else{

   echo  " Created tablenn";
}

$sql = "INSERT INTO OE.Catalog VALUES('catalog1', 'Oracle Magazine',
   'Oracle Publishing', 'Nov-Dec 2004', 'Database Resource Manager',
   'Kimberly Floss')";
   $stmt = oci_parse($connection, $sql);
if (!$stmt) {
   $e = oci_error($connection);
   echo htmlentities($e['message']);
}

$r = oci_execute($stmt);
if (!$r) {
   $e = oci_error($stmt);
   echo htmlentities($e['message']);
}else{

   echo " Added a rownn";
}
$sql = "INSERT INTO OE.Catalog VALUES('catalog2', 'Oracle Magazine',
   'Oracle Publishing', 'March-April 2005',
   'Starting with Oracle ADF ', 'Steve Muench')";

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

if (!$stmt) {
   $e = oci_error($connection);
   echo htmlentities($e['message']);
}
$r = oci_execute($stmt);
if (!$r) {
   $e = oci_error($stmt);
   echo htmlentities($e['message']);
}else{

   echo " Added a rownn";
}

?>

Start Apache web server, if not already started. Run the PHP script in a browser with URL http://localhost/createOracleTable.php.A database table, Catalog, gets generated and data gets added to the table, as shown in Figure 1.

Figure 1: A database table gets generated and data gets added.

Retrieving Data from the Database

Next, you will use the PHP Oracle extension to connect with the Oracle database and retrieve data from the database table Catalog that you created in the previous section. Create a PHP script, retrieveOracleData.php, in the Apache2 server’s htdocs directory. Define variables $username, $password, and $db for Oracle database username, password, and database. Specify the $db variable value as the database SID value in the <Oracle10g>/NETWORK/ADMIN/tnsnames.ora file.

$username='OE';
$password='pw';

$db='(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
   )
)';

Obtain a connection with the database using the oci_connect function.

$connection = oci_connect($username, $password, $db);

Prepare an Oracle statement to select rows of data. The oci_parse(connection, query) function is used to compile a statement. The prepared SQL statement uses a bind variable, :journal, for the JOURNAL column.

$stmt = oci_parse($connection, "SELECT * from OE.CATALOG
   WHERE JOURNAL=:journal");

Bind a value to the :journal bind variable using the oci_bind_by_name() function.


$journal='Oracle Magazine';
oci_bind_by_name($stmt, ":journal", $journal);

Run the SQL query with the oci_execute(statement) function.

$r = oci_execute($stmt);

Fetch all the rows in the result set using the oci_fetch_all(statement, result) function.

$nrows = oci_fetch_all($stmt, $results);

The oci_fetch_all function returns the number of rows in the result set. If the number of rows is more than 0, create an HTML table to output the rows in the result set. By default, the oci_fetch_all() function fetches data by column. Iterate over the rows, and add the column data to the HTML table.


for ($i = 0; $i < $nrows; $i++) {
   echo "<tr>n";
   foreach ($results as $data) {
      echo "<td>$data[$i]</td>n";
   }
   echo "</tr>n";
}

You used the oci_fetch_all() method to retrieve data from the result set. But, one of the other fetch methods; oci_fetch(), oci_fetch_row(), oci_fetch_array(), or oci_fetch_object(), or oci_fetch_assoc() also may be used. The PHP script retrieveOracleData.php is shown in Listing 2.

Listing 2: retrieveOracleData.php

<?php

$username='OE';
$password='pw';

$db='(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
   )
)';

$connection = oci_connect($username, $password, $db);
$stmt = oci_parse($connection, "SELECT * from OE.CATALOG
   WHERE JOURNAL=:journal");

$journal='Oracle Magazine';
oci_bind_by_name($stmt, ":journal", $journal);

$r = oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

if($nrows>0){
echo "<table border><tr><th>Catalog

Id</th><th>Journal</th><th>Publisher</th><th>Edition</th>
   <th>Title</th><th>Author</th></tr>";

for ($i = 0; $i < $nrows; $i++) {
   echo "<tr>n";
   foreach ($results as $data) {
      echo "<td>$data[$i]</td>n";
   }
   echo "</tr>n";
}

echo "</table>";

}

?>

Next, run the retrieveOracleData.php script in the Apache web server with the URL http://localhost/retrieveOracleData.php. The Oracle database data gets retrieved and displayed in an HTML table, as shown in Figure 2.

Oracle2.jpg Data gets retrieved and displayed in an HTML table.

Conclusion

PHP provides the OCI8 extension to connect to an Oracle database and runs SQL queries in the database. In this article, you installed the OCI8 extension, established a connection to Oracle database, created a database table, and retrieved database table data.

About the Author

Deepak Vohra (dvohra09@yahoo.com) is a Sun Certified Java Programmer and a Sun Certified Web Component Developer, and has published in devx, FTPOnline, JavaBoutique, ONJava, and java.net.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories