Archive for the ‘reference cursor’ tag
PL/SQL NDS Reference Cursor with Record Collection
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.
PHP code to read a PL/SQL reference cursor
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; } ?> |