Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

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

Advertisements

Written by maclochlainn

May 12, 2008 at 5:17 am

Posted in PHP

Tagged with , ,

3 Responses

Subscribe to comments with RSS.

  1. […] PL/SQL NDS Reference Cursor with Record Type Posted in Oracle by maclochlainn on May 31st, 2008 Somebody got to this blog looking for a way to write a Native Dynamic SQL (NDS) statement that returned a system reference cursor. I created an image file to show how to do that. I borrowed the code from Chapter 11 of Oracle Database 11g PL/SQL Programming. The only problem with this is that it’s a PL/SQL only solution typically. You can write it as a function or procedure, and then use a Java or PHP program to display the reference cursor in a web page. You can check this previous post for a PHP example. […]

  2. […] it appears that weakly typed cursors have little use anywhere but in an OCI program. You can find an example of using a weakly typed cursor in a PHP program on this blog […]

  3. […] reference cursor. That’s why they’re useful in OCI programming languages. You can find an example of using a weakly typed cursor in a PHP program on this blog […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: