MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘PL/SQL reference cursor’ tag

Adapter or not adapter that’s the question

with 3 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.

BEGIN
  FOR i IN (SELECT *
            FROM TABLE(use_of_input_cursor(weakly_typed_cursor('Harry')))) LOOP
    DBMS_OUTPUT.put_line('Item Title    ['||i.item_title||']');
    IF i.item_subtitle IS NOT NULL THEN
      DBMS_OUTPUT.put_line('Item Subtitle ['||i.item_subtitle||']');
    END IF;
  END LOOP;
END;
/

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.

  // Declare a PL/SQL execution command.
  $stmt = "BEGIN
             world_leaders.get_presidents(:term_start
                                         ,:term_end
                                         ,:country
                                         ,:return_cursor);
           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,':term_start',$t_start);
  oci_bind_by_name($s,':term_end',$t_end);
  oci_bind_by_name($s,':country',$country);
 
  // 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.
  oci_execute($s);
 
  // Access the returned cursor.
  oci_execute($rc);

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

  // 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>';

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 31st, 2008 at 11:00 pm

Posted in Oracle,PHP

Tagged with , ,