Nice “how-to” install OPAL on Ubuntu 8 Server

I noticed that somebody posted instructions and some scripts to install the OPAL (Oracle, Perl/PHP, Apache, Linux) in the OTN forum. I haven’t had a chance to run though it yet. Thought I’d point you to it directly. If you want the forum, go here. He’d like feedback in the forum.

PHP code to read a PL/SQL reference cursor

The code is updated and copiable on the new blog.

The following demonstrates how to read a PL/SQL reference cursor in a PHP program. The reference cursor function is defined in the Pipelined Functions & PL/SQL Tables blog page. I’ve commented it to the hilt for those new to PHP.

The reference cursor maintains a separate connection to the database to access the reference cursor. You also use the oci_fetch_assoc() function to get the data. That strip_special_characters() function lets you format your call to the PL/SQL program and remove non-parsing line returns and tabs before running the oci_parse() function.

<?php
  // Return successful attempt to connect to the database.
  if ($c = @oci_connect("plsql","plsql","orcl"))
  {
    // Declare input variables.
    (isset($_GET['table'])) ? $table = (int) $_GET['table'] : $table = 'ITEM';
    (isset($_GET['column'])) ? $column = (int) $_GET['column'] : $column = 'ITEM_TYPE';
    // Declare a PL/SQL execution command.
    $stmt = "BEGIN
               :return_cursor := get_common_cursor(:table,:column);
             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);
    // Declare a return cursor for the connection.
    $rc = oci_new_cursor($c);
    // Bind PHP variables to the OCI input or in mode variables.
    oci_bind_by_name($s,':table',$table);
    oci_bind_by_name($s,':column',$column);
    // Bind PHP variables to the OCI output or in/out mode variable.
    oci_bind_by_name($s,':return_cursor',$rc,-1,OCI_B_CURSOR);
    // Execute the PL/SQL statement & reference cursor.
    oci_execute($s);
    oci_execute($rc);
    // Print the table header with known labels.
    print '<table border="1" cellpadding="3" cellspacing="0">';
    // Set dynamic labels control variable true.
    $label = true;
    // Read the contents of the reference cursor.
    while($row = oci_fetch_assoc($rc))
    {
      // Declare header and data variables.
      $header = "";
      $data = "";
      // Read the reference cursor into a table.
      foreach ($row as $name => $column)
      {
        // Capture labels for the first row.
        if ($label)
        {
          $header .= '<td class="e">'.$name.'</td>';
          $data .= '<td class="v">'.$column.'</td>';
        }
        else
          $data .= '<td class=v>'.$column.'</td>';
      }
      // Print the header row once.
      if ($label)
      {
        print '<tr>'.$header.'</tr>';
        $label = !$label;
      }
      // Print the data rows.
      print '<tr>'.$data.'</tr>';
    }
    // Print the HTML table close.
    print '</table>';
    // 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 character only strings.
    return $out;
  }
?>

Oracle PHP/AJAX Framework

While writing Oracle Database 11g PL/SQL Programming book last year, I collaborated with Lee Barney on a PHP/AJAX framework. Our Oracle Database AJAX & PHP Web Application Development book covers the framework. You can find a new and improved version at SourceForge.net. Lee maintains the code and continues to improve it. I’m sure he’d welcome suggestions and collaboration. We see it as very useful to AJAX web development, and to the Open Source community.