MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

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

3 Responses to 'Adapter or not adapter that’s the question'

Subscribe to comments with RSS or TrackBack to 'Adapter or not adapter that’s the question'.

  1. Hello Michael,

    Lots of interesting material in your blog! Thanks for sharing…

    One thing. You write “You can assign a PL/SQL record structure through a PIPELINED function to an aggregate table, which can be read by SQL.”

    I believe you mean “through a table function”. A pipelined table function is a specialized sort of table function, which is very helpful for parallelizing execution of queries that call a table function, and for reducing pereceived elapsed time of data retrieval.

    It does not, however, provide a benefit over “regular” table functions except in those circumstances. And pipelined functions can ONLY be called from within queries.

    Regards, Steven Feuerstein

  2. Hey Steven,

    You make a great point! I’ve updated the reference to: Pipelined table function. That’s definitely more consistent with Chapter 12 of the PL/SQL Language Reference. I tried to make that point about where they’re most useful in an earlier post but clarification is always welcome. Thanks!

    maclochlainn

    1 Nov 08 at 3:32 pm

  3. This applies the Adapter pattern in a bit of a twist, more like a force fit. It shows you how to wrap a system reference cursor return data type and convert it to a PL/SQL table of records.

Leave a Reply