<html>
<! QueryLobPLSQL.php                                          >
<! Chapter 15, Oracle Database XE PHP Web Programming         >
<! by Michael McLaughlin                                      >
<!                                                            >
<! This script demonstrates how to return a CLOB from a       >
<! PL/SQL stored procedure.                                   >
<head>
<title>
  Chapter 15: QueryLobPLSQL.php
</title>
<style>
.e {background-color: #ccccff; font-weight: bold; color: #000000;}
.v {background-color: #cccccc; color: #000000;}
</style>
</head>
<body>
<?php
  // Return successful attempt to connect to the database.
  if ($c = @oci_connect("php","php","xe"))
  {
    // Declare input variables.
    (isset($_GET['id'])) ? $id = (int) $_GET['id'] : $id = 1;
    (isset($_GET['name'])) ? $name = $_GET['name'] : $name = "Washington";

    // Declare a PL/SQL execution command.
    $stmt = "BEGIN
               biography.read_biography(:id,:biography);
             END;";

    // Strip special characters to avoid ORA-06550 and PLS-00103 errors.
    $stmt = strip_special_characters($stmt);

    // Parse a query through the connection.
    $s = oci_parse($c,$stmt);

    // Define a descriptor for a CLOB.
    $rlob = oci_new_descriptor($c,OCI_D_LOB);

    // Define a variable name to map to CLOB descriptor.
    oci_define_by_name($s,':biography',$rlob,SQLT_CLOB);

    // Bind PHP variables to the OCI types.
    oci_bind_by_name($s,':id',$id,-1,SQLT_INT);
    oci_bind_by_name($s,':biography',$rlob,-1,SQLT_CLOB);

    // Execute the PL/SQL statement.
    if (oci_execute($s,OCI_DEFAULT))
    {
      // Returns zero when CLOB is null or empty value.
      if ($size = @$rlob->size())
        $data = $rlob->read($size);
      else
        $data = "&nbsp;";

     // End the transaction scope.
      oci_commit($c);

      // Format HTML table to display biography.
      $out = '<table border="1" cellpadding="3" cellspacing="0">';
      $out .= '<tr>';
      $out .= '<td align="center" class="e">Biography of '.$name.'</td>';
      $out .= '</tr>';
      $out .= '<tr>';
      $out .= '<td class="v">'.$data.'</td>';
      $out .= '</tr>';
      $out .= '</table>';
      $out .= '</table>';
    }

    // Print the HTML table.
    print $out;

    // Disconnect from database.
    oci_close($c);
  }
  else
  {
    // Assign the OCI error and format double and single quotes.
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }

  // Strip special characters, like carriage or line returns and tabs.
  function strip_special_characters($str)
  {
    $out = "";
    for ($i = 0;$i < strlen($str);$i++)
      if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) &&
          (ord($str[$i]) != 13))
        $out .= $str[$i];

    // Return pre-parsed SQL statement.
    return $out;
  }
?>
</body>
</html>
