PHP Quickstart

Overview

The following covers the essential steps to setup a PHP development environment for Oracle 10g\11g. PHP is implemented here via WampServer. WampServer is a package of applications consisting of Apache, MySQL and PHP. It is used by many PHP professionals because it is easy to: install, administrate and keep up-to-date with the latest versions of Apache and PHP.

Prerequisites

Oracle database or at least the Oracle client software (standard or instant) has been installed.
The Oracle environment variables are set: ORACLE_HOME etc.

Installing WampServer

Once these steps are completed a WampServer tray icon will be displayed.

Download the latest version of WampServer.
http://www.wampserver.com/en/
Run the installer.
Example: Start -> Run -> C:\temp\WampServer2.0i.exe
Welcome
Next
License Agreement
Accept
Destination Location
C:\wamp
Additional Tasks
[x] Create a Quick Launch icon
[x] Create a Desktop icon
Ready to Install
Install

Browser Prompt: Use <browser name> as the default with WampServer?
Yes
Firewall Prompt:
Unblock Apache
PHP Mail Parameters
SMTP: <your SMTP server IP or DNS>
Email: <email address to get messsages>
Finish
[x] Launch WampServer Now
Finish

Test WampServer

Left click on the WampServer tray icon and note the listed menu options.
From the WampServer menu select LocalHost
Webpage should launch displaying server configuration.

Configure WampServer\PHP for Oracle

This is the minimum configuration required to enable PHP access to Oracle databases.
In some instances the GUI menu will not edit the correct php.ini. The correct one is: C:\wamp\bin\php\php.ini

From the WampServer menu select php.ini.

In the php.ini uncomment the following lines:

   extension=php_oci8.dll      ;Use with Oracle 10g 
   ;extension=php_oci8_11g.dll ;Use with Oracle 11g 
   extension=php_pdo_oci.dll   ;To use PDO (PHP Data Objects)

Set php.ini for your SMTP server.
Usage: mail('scott.tiger@example.com', 'My Subject', 'Message here.');
```
   [mail function]
   SMTP = 192.168.1.42
   smtp_port = 25
```

In the php.ini increase timeouts from default values.

   max_input_time = 120
   default_socket_timeout = 300

Restart All Services
From the WampServer menu select: Restart All Services
Test PHP with Oracle
- Create and run a simple database connection test page.
- Run a test page that pulls records from a table.

Console Applications

To run PHP console applications do the following:

Add the path to the php.exe to the PATH environment variable.
Example entry to add: C:\wamp\bin\php\php5.3.0;
Use the -q option when running scripts.
Example: php -q myscript.php

Web Application Sample Code

<html>
<body>
<h2>Simple Connection Test</h2>

<?php
// Connect to Oracle Database
$connection = oci_connect("hr", "mypassword", "192.168.1.42/DB01");

// Create Cursor
$cursor = oci_parse($connection,  "SELECT sysdate FROM dual");
oci_execute ($cursor);

// Output
while ($row = oci_fetch_array ($cursor)) {
   // Two ways to output the same value of a field.
   echo $row[0] . "<br>";
   echo $row['SYSDATE'] . "<br>";
}

// Close Connection
oci_close($connection);
?>

</body>
</html>

<html>
<body>
<h2>Table Column Listing</h2>

<?php
// Connect to Oracle Database
$connection = oci_connect("hr", "mypassword", "192.168.1.42/DB01");

// Create Cursor
$SQL     = "SELECT emp_name FROM hr.emp ORDER BY emp_name";
$cursor  = oci_parse($connection,  $SQL);
oci_execute ($cursor);

// Output - column name must be UPPER case here.
while ($row = oci_fetch_array ($cursor)) {
   echo $row['EMP_NAME'] . "<br>";
}

// Close Connection
oci_close($connection);
?>

</body>
</html>

<html>
<body>
<h2>Database Error Trapping</h2>

<?php
// Connect to Oracle Database
if (! $connection = @oci_connect("hr", "mypassword", "192.168.1.42/DB01") ) {
  onDBError( $err=oci_error() );
}

// Create Cursor
// Note: oci_parse() returns false only if there is a connection problem.
$SQL    = "SELECT emp_name FROM hr.emp ORDER BY emp_name";
$cursor = oci_parse($connection, $SQL);
if (! @oci_execute($cursor) ) {
    onDBError( $err=oci_error($cursor) );
}

// Output
while ($row = oci_fetch_array ($cursor)) {
   echo $row['EMP_NAME'] . "<br>";
}

// Close Connection
if (! @oci_close($connection) ) {
    onDBError( $err=oci_error($connection) );
}

function onDBError($error) {
   echo "<table border='1'>";
   echo "   <tr>";
   echo "      <th colspan='2' bgcolor='red'>";
   echo "         <font color='white'>OCI Error</font>";
   echo "      </th>";
   echo "   </tr>";
   echo "   <tr><td >Code</td>      <td>" . $error['code']     . " </td></tr>";
   echo "   <tr><td>Message</td>    <td>" . $error['message']  . " </td></tr>";
   echo "   <tr><td>Position</td>   <td>" . $error['offset']   . " </td></tr>";
   echo "   <tr><td>Statement</td>  <td>" . $error['sqltext']  . " </td></tr>";
   echo "</table>";
   exit;
}
?>
</body>
</html>

<html>
<body>
<h2>PDO Example</h2>

<?php
// Init Vars
$ora_username  = "scott";
$ora_password  = "tiger"';
$ora_host      = "192.168.1.42";
$ora_service   = "DB01";
$ora_port      = "1521";

$ora_tns       = "oci:dbname=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)";
$ora_tns      .= "(HOST = ". $ora_host .")(PORT = " . $ora_port.")))";
$ora_tns      .= "(CONNECT_DATA = (SERVICE_NAME = " . $ora_service . ")))";

// Connect to Database
$db  = new PDO($ora_tns, $ora_username, $ora_password);
$SQL = "SELECT * FROM emp ORDER BY emp_id";

// Output
foreach ($db->query($SQL) as $row) {
   print $row['EMP_NAME'] . '<br>';
}

</body>
</html>

PDO Snippet: Get One Value
// Init Vars
$schema         = "FI42";
$StartDate      = "01-01-2009";
$EndDate        = "12-31-2009";
$TotalDocuments = 0;

// Get Total Documents
$SQL  = "SELECT count(*) count FROM " . $schema . ".documents ";
$SQL .= "WHERE  creationdate >= to_date('" . $StartDate . ".0000.00','MM-DD-YYYY.HH24MI.SS') ";
$SQL .= "AND    creationdate <= to_date('" . $EndDate   . ".2359.59','MM-DD-YYYY.HH24MI.SS') ";
foreach( $db->query( $SQL ) as $row ) { $TotalDocuments = $row[0]; }

echo $TotalDocuments;

Custom Error Handler
function ErrorHandler($errno, $errstr) {
   echo "<table class='error'>";
   echo "   <tr><th class='error'>Error</th><td class='error'>"  . $errno  . "</td></tr>";
   echo "   <tr><th class='error'>String</th><td class='error'>" . $errstr . "</td></tr>";
   if ($errno = 2) {
      $msg = "Could not execute query with given input.";
   } else {
      $msg = " ";
   }
   echo "   <tr><th class='error'>Detail</th><td class='error'>" . $msg . "</td></tr>";
   echo "</table>";
}

Usage Example
/* Main Processing Loop */
set_error_handler("ErrorHandler"); 
foreach ($db->query($SQL) as $row) {
   echo "<tr>";
   echo "   <td class='data'>" . $row[0] . " </td>";
   echo "   <td class='data'>" . $row[1] . " </td>";
   echo "</tr>";
} //foreach

Disclaimer

Contents