Archive for the ‘oci_new_cursor($c)’ tag
Adapter or not adapter that’s the question
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.