Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Archive for the ‘PHP’ Category

Adapter or not adapter that’s the question

with 2 comments


The Adapter pattern in Object-Oriented OO programming is missing when it comes to how you can read, pass, and manage a PL/SQL system reference cursor. At least, it’s missing for PL/SQL native development. It is available through the OCI but only as a multiple dimensional array of strings.

Here’s an illustration of the Adapter Pattern (courtesy of the Design Patterns book by Gamma, Helm, Johnson, and Vlissides):

There are a few ways to handle reference cursor in PL/SQL. I’ve updated the earlier blog to more completely cover the options. In all cases within a PL/SQL environment, you must know the target type of the PL/SQL record structure. The target for converting a PL/SQL reference cursor is a a PL/SQL record structure.

You can assign a PL/SQL record structure through a PIPELINED table function to an aggregate table, which can be read by SQL. The following query lets you read an aggregate table back into a PL/SQL structure but begs the question of why you’d want to do that. The actual query by itself is in the updated blog entry linked above.

The OCI8 driver lets you take the system reference cursor from a PL/SQL block and translate it to a multidimensional array of strings. The following (borrowed from my book on PHP programming with Oracle) demonstrates how to open a statement and a reference cursor, which lets you manage the OUT mode parameter (covered in this earlier post) variable of a PL/SQL reference cursor. Our thanks should go to the OCI team because they see the world of interactivity.

You can query the results of the reference cursor ($rc), like this:

Moreover, it would be wonderful if Oracle let you implement a full Adapter pattern but there are constructive ways to work with what we’ve got now. You actually get a bit more through the JDBC implementation but that’s for another blog I guess. As to adapter or not adapter, it’s clearly not.

Written by maclochlainn

October 31, 2008 at 11:00 pm

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

leave a comment »


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.

Written by maclochlainn

July 20, 2008 at 9:16 pm

Posted in PHP

Tagged with , , , , ,

PHP code to read a PL/SQL reference cursor

with 3 comments


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;
  }
?>

Written by maclochlainn

May 12, 2008 at 5:17 am

Posted in PHP

Tagged with , ,