MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Beats a reference cursor

with 2 comments

You can’t beat play’n around with the technology. It seems that each time I experiment with something to answer a question, I discover new stuff. So, I really appreciate that Cindy Conlin asked me to net out why a PL/SQL Pipelined Table function existed at UTOUG Training Days 2009.

I found that Java and PHP have a great friend in Pipelined Table functions because when you wrap them, you can simplify your code. While a reference cursor lets you return the product of a bulk operation, it requires two hooks into the database. One for the session connection and another for the connection to the system reference cursor work area. While this was a marvelous feature of the OCI8 library, which I duly noted in my Oracle Database 10g Express Edition PHP Web Programming book, there’s a better way.

The better way is a Pipelined Table function because you can query it like you would a normal table or view. Well, not exactly but the difference involves the TABLE function, and it is really trivial.

When you call a Pipelined Table function, you only need to manage a single hook into the database. That hook is for the session connection. You can find a full (really quite detailed) treatment of Table and Pipelined Table functions in this blog page. Building on that blog page, here’s a simple PHP program that demonstrates the power of leveraging the SQL context provided by a Pipelined Table function.

<?php
  // Connect to the database.
  if ($c = @oci_connect("plsql","plsql","orcl"))
  {
    // Parse a query to a resource statement.
    $s = oci_parse($c,"SELECT * 
                       FROM TABLE(get_common_lookup_plsql_table('ITEM','ITEM_TYPE'))");
 
    // Execute query without an implicit commit.
    oci_execute($s,OCI_DEFAULT);
 
    // Open the HTML table.
    print '<table border="1" cellspacing="0" cellpadding="3">';
 
    // Read fetched headers.
    print '<tr>';
    for ($i = 1;$i <= oci_num_fields($s);$i++)
      print '<td class="e">'.oci_field_name($s,$i).'</td>';
    print '</tr>';
 
    // Read fetched data.
    while (oci_fetch($s))
    {
      // Print open and close HTML row tags and columns data.
      print '<tr>';
      for ($i = 1;$i <= oci_num_fields($s);$i++)
        print '<td class="v">'.oci_result($s,$i).'</td>';
      print '</tr>';
    }
 
    // Close the HTML table.
    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 />";
  }
?>

You’ll notice that all the information that is expected from a query against a table or view is also available from the result of Pipelined Table function. That’s because the Pipeline Table function actually places the internal record structure of a PL/SQL collection into the SQL context along with the data.

This sample PHP program produces the following XHTML output:

COMMON_LOOKUP_ID COMMON_LOOKUP_TYPE COMMON_LOOKUP_MEANING
1013 DVD_FULL_SCREEN DVD: Full Screen
1014 DVD_WIDE_SCREEN DVD: Wide Screen
1015 GAMECUBE Nintendo GameCube
1016 PLAYSTATION2 PlayStation2
1019 VHS_DOUBLE_TAPE VHS: Double Tape
1018 VHS_SINGLE_TAPE VHS: Single Tape
1017 XBOX XBOX

 

Naturally, you can parameterize your PHP program and add bind variables to make this more dynamic. An example of parameterizing the call to a Pipelined Function is provided in the next program example.

You would use the following URL to call the dynamic PHP program:

http://mclaughlin11g/GetCommonLookup.php?table=ITEM&column=ITEM_TYPE

The working PHP program code is:

<?php
  // Declare input variables.
  (isset($_GET['table'])) ? $table = $_GET['table'] : $table = "ITEM";
  (isset($_GET['column'])) ? $column = $_GET['column'] : $column = 'ITEM_TYPE';
 
  // Connect to the database.
  if ($c = @oci_connect("plsql","plsql","orcl"))
  {
    // Parse a query to a resource statement.
    // Don't use table and column because they're undocumented reserved words in the OCI8.
    $s = oci_parse($c,"SELECT * 
                       FROM TABLE(get_common_lookup_plsql_table(:itable,:icolumn))");
 
    // Bind a variable into the resource statement.
    oci_bind_by_name($s,":itable",$table,-1,SQLT_CHR);
    oci_bind_by_name($s,":icolumn",$column,-1,SQLT_CHR);
 
    // Execute query without an implicit commit.
    oci_execute($s,OCI_DEFAULT);
 
    // Open the HTML table.
    print '<table border="1" cellspacing="0" cellpadding="3">';
 
    // Read fetched headers.
    print '<tr>';
    for ($i = 1;$i <= oci_num_fields($s);$i++)
      print '<td class="e">'.oci_field_name($s,$i).'</td>';
    print '</tr>';
 
    // Read fetched data.
    while (oci_fetch($s))
    {
      // Print open and close HTML row tags and columns data.
      print '<tr>';
      for ($i = 1;$i <= oci_num_fields($s);$i++)
        print '<td class="v">'.oci_result($s,$i).'</td>';
      print '</tr>';
    }
 
    // Close the HTML table.
    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 />";
  }
?>

You may note that the parameter values (placeholders or bind variables inside the SQL statement) are prefaced with an i. That’s because TABLE and COLUMN are restricted key words in the context of OCI8, and their use triggers an ORA-01036 exception.

This makes PHP more independent of the OCI8 library and easy to cross port to other databases if that’s a requirement. Hope this helps some folks.

Written by maclochlainn

March 19th, 2009 at 12:31 am

Posted in OPAL,Oracle,PHP,pl/sql,sql

«
»