C Using a PL/SQL Procedure With PHP

This section provides an example of the use of a PL/SQL stored procedure with PHP.

This section does not provide detailed information about PHP or its use with Oracle Database Express Edition. For a brief summary of PHP and links to resources for PHP, see "PHP" .

This section contains the following topics:

PHP and Oracle Database XE
Creating a PHP Program That Calls a PL/SQL Stored Procedure

PHP and Oracle Database XE

PHP is a widely-used, open-source, interpretive, HTML-centric, server-side scripting language. PHP is especially suited for Web development and can be embedded into HTML pages. Zend Core for Oracle, developed in partnership with Zend Technologies, enables application development using PHP with Oracle Database XE.

To run the PHP program in Example C-1, you need to have Oracle Database XE, Apache 1.3.x or later, and Zend Core for Oracle installed on your computer.

Creating a PHP Program That Calls a PL/SQL Stored Procedure

Example C-1 shows the PHP code that searches for and displays employee information based on the first and last name of an employee. The PHP program first gathers user input for the first and last name of an employee. The text input can be the full names or substrings of the first and last names of the employee. With valid input, a connection is made to Oracle Database XE, the get_emp_info procedure is called to search for the employee records that match the input strings, and then the results are displayed.

The PL/SQL get_emp_info procedure is created in Example 5-15 and it determines the result set for the cursor variable (REF CURSOR) that is passed to the PHP program. The package specification in Example 5-13 defines the cursor variable (my_refcur_typ) that is declared in the get_emp_info procedure. A cursor variable can be passed as a parameter to other packages, procedures, and functions. For information about cursor variables (REF CURSORs) see "Cursor Variables (REF CURSORs)". For information about using types in package specifications, see "Accessing Types in Packages".

The PHP program in Example C-1 is intended only to be an illustration of the use of a PL/SQL stored procedure with PHP. It does not include error checking or many other PHP features.

Save the PHP program in Example C-1 as emp_search.php. Before running the PHP program in Example C-1, the PL/SQL get_emp_info procedure in Example 5-15 must be created by the HR user.

Example C-1 Creating a PHP Program for Use With a PL/SQL Procedure

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
       "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
  <title>Search for Employee Information</title>
</head>
<body bgcolor="#EEEEEE">
<h2>Employee Search by First and Last Names</h2>

<?php 

function get_input($first = "", $last = "") 
{
  echo <<<END
  <form action="emp_search.php" method="post">
  First Name:
  <input type="text" name="first" value="$first">
  <br>
  Last Name:
  <input type="text" name="last" value="$last">
  <p> 
  <input type="submit">
  </form>
END;
} 

if(!isset($_REQUEST['first'])) {
   echo "Enter text in both the first and last name fields.<br>
         You can enter the complete name or an initial substring.<p>"; 
   get_input();
}
else {
  // check whether the input fields are empty before continuing
  if (empty($_REQUEST['first']) or empty($_REQUEST['last'])) {
    echo "You did not enter text in both 
          fields, please re-enter the information.<p>"; 
    get_input($_REQUEST['first'], $_REQUEST['last']);
  }
  else {
    // if text has been entered in both input fields, then
    // create a database connection to Oracle XE using 
    // password hr for user HR with a local connection to the XE database
    $conn = oci_connect('hr', 'hr', '//localhost/XE');

    // execute the function that calls the PL/SQL stored procedure 
    $emp = get_employees($conn, $_REQUEST['first'], $_REQUEST['last']);

    // display results
    print_results($emp, 'Employee Information');

    // close the database connection
    oci_close($conn);
  }
} 

// this functions calls a PL/SQL procedure that uses a ref cursor to fetch records
function get_employees($conn, $firstname, $lastname)
{
  // execute the call to the stored PL/SQL procedure
  $sql = "BEGIN get_emp_info(:firstname, :lastname, :refcur); END;";
  $stmt = oci_parse($conn, $sql);

  // bind the first and last name variables
  oci_bind_by_name($stmt, ':firstname', $firstname, 20);
  oci_bind_by_name($stmt, ':lastname', $lastname, 25);

  // bind the ref cursor
  $refcur = oci_new_cursor($conn);
  oci_bind_by_name($stmt, ':REFCUR', $refcur, -1, OCI_B_CURSOR);

  // execute the statement
  oci_execute($stmt);

  // treat the ref cursor as a statement resource
  oci_execute($refcur, OCI_DEFAULT);
  oci_fetch_all($refcur, $employeerecords, null, null, OCI_FETCHSTATEMENT_BY_ROW);

  // return the results
  return ($employeerecords);
}

// this function prints information in the returned records
function print_results($returned_records, $report_title)
{
  echo '<h3>'.htmlentities($report_title).'</h3>';
  if (!$returned_records) {
    echo '<p>No Records Found</p>';
  }
  else {
    echo '<table border="1">';
    // print one row for each record retrieved
    // put the fields of each record in separate table cells
    foreach ($returned_records as $row) {
      echo '<tr>';
      foreach ($row as $field) {
        print '<td>'.
            ($field ? htmlentities($field) : '&nbsp;').'</td>';
      }
    }
    echo '</table>';
  }
}

?>

</body>
</html>

After you save the PHP program (emp_search.php) and access the file in a Web browser, you are prompted to enter a first and last name. After you enter text for the first and last name, click the Submit Query button.

Description of the illustration xe_php_input.gif

If you have entered text in both the first and last name fields, the employees table is searched and the results are displayed using the first version of the get_emp_info procedure shown in Example 5-15.

Description of xe_php_output.gif follows

Description of the illustration xe_php_output.gif

After you have updated the get_emp_info procedure shown in Example 5-15, you can run the PHP emp_search.php program again and check the results. If you have entered text in both the first and last name fields, the employees table is searched and the results are displayed using the updated procedure.

Description of xe_php_output2.gif follows

Description of the illustration xe_php_output2.gif