Using PHP and Oracle Database 12c Implicit Result Sets

Note: This post describes functionality in PHP OCI8 2.0.0-devel. Functionality and naming is subject to change.

The new Oracle Database 12c "Implicit Result Sets" (IRS) feature allows query results to be returned from a stored PL/SQL procedure (or a PL/SQL anonymous block) without requiring special PHP code. Support for IRS is available in PHP's OCI8 2.0.0-devel extension when it is compiled and used with Oracle Database 12c. (OCI8 2.0 can be compiled and used with other versions of Oracle Database but the available feature set is reduced).

Recall that a normal Oracle query can be performed in PHP with a parse-execute-fetch loop like:

<?php
$c = oci_connect('hr', 'welcome', 'localhost/pdborcl');
$sql = "select city from locations where rownum < 4";
$s = oci_parse($c, $sql);
oci_execute($s);
while (($row = oci_fetch_row($s)) != false) {
    foreach ($row as $item) {
        echo $item . " ";
    }
    echo "\n";
}
?>

The output is:

Beijing
Bern
Bombay

With PHP OCI8 2.0.0-devel and Oracle Database 12c, the same results can be obtained by changing $sql to an anonymous PL/SQL block (or by calling a previously created stored PL/SQL procedure) that uses DBMS_SQL.RETURN_RESULT like:

$sql =
 "declare
    c1 sys_refcursor;
  begin
    open c1 for select city from locations where rownum < 4;
    dbms_sql.return_result(c1);
  end;";

With this statement change, the previous PHP code fetches the query results without needing any logic alterations. In older versions without IRS, the PL/SQL and PHP code would have to handle a REF CURSOR parameter.

The real fun begins when you have multiple DBMS_SQL.RETURN_RESULT calls in the same PL/SQL block:

$sql =
 "declare
    c1 sys_refcursor;
  begin
    open c1 for select city from locations where rownum < 4;
    dbms_sql.return_result(c1);
    open c1 for select first_name, last_name from employees where rownum < 4;
    dbms_sql.return_result(c1);
  end;";

The PHP fetch loop handles this nicely and sequentially fetches rows from both queries:

Beijing  
Bern  
Bombay  
Ellen Abel  
Sundar Ande  
Mozhe Atkinson  

Only oci_fetch_array(), oci_fetch_assoc(), oci_fetch_object() and oci_fetch_row() (but not oci_fetch() or oci_fetch_all()) will automatically fetch IRS data like this. [This is a semi-arbitrary decision trying to balance the increased amount of code complexity and testing versus the expected use of the feature. If there is strong demand this decision can be revisited.]

To process each of the query results independently in PHP, use the newly introduced oci_get_implicit_resultset() function. This takes the parent statement resource, e.g. $s, and returns a PHP statement resource corresponding to the first result set. Each time oci_get_implicit_resultset() is subsequently called, it returns the next result set. When there are no more result sets, it returns false. Because oci_get_implicit_resultset() returns a statement resource, you can use any of the oci_fetch_* functions to get rows.

For example, to print appropriate column names above each row's items:

<?php
$c = oci_connect('hr', 'welcome', 'localhost/pdborcl');
$sql =
 "declare
    c1 sys_refcursor;
  begin
    open c1 for select city from locations where rownum < 4;
    dbms_sql.return_result(c1);
    open c1 for select first_name, last_name from employees where rownum < 4;
    dbms_sql.return_result(c1);
  end;";
$s = oci_parse($c, $sql);
oci_execute($s);
while (($s2 = oci_get_implicit_resultset($s))) {
    // Now treat $s2 as a distinct query statement resource
    $ncols = oci_num_fields($s2);
    for ($i = 1; $i <= $ncols; ++$i) {
        $colname = oci_field_name($s2, $i);
        echo $colname . " ";
    }
    echo "\n";
    while (($row = oci_fetch_row($s2)) != false) {
        foreach ($row as $item) {
            echo $item . " ";
        }
        echo "\n";
    }
}
?>

The output is:

CITY  
Beijing  
Bern  
Bombay  
FIRST_NAME LAST_NAME  
Ellen Abel  
Sundar Ande  
Mozhe Atkinson  

You can also do things like calling oci_set_prefetch() on the IRS statement resources, or calling oci_get_implicit_resultset() multiple times before beginning to fetch row data:

$sql =
 "declare
    c1 sys_refcursor;
  begin
    open c1 for select city from locations where rownum < 4;
    dbms_sql.return_result(c1);
    open c1 for select first_name, last_name from employees where rownum < 4;
    dbms_sql.return_result(c1);
  end;";
$s = oci_parse($c, $sql);
oci_execute($s);
$s1 = oci_get_implicit_resultset($s);
$s2 = oci_get_implicit_resultset($s);
$row1_1 = oci_fetch_row($s1);
$row2_1 = oci_fetch_row($s2);
$row1_2 = oci_fetch_row($s1);
$row2_2 = oci_fetch_row($s2);
. . .

There are more examples in the OCI8 test suite (see the tests sub-directory of the source bundle).

When would you used an IRS? Some cases are:

  • Migrating an application to Oracle Database, where the application currently makes use of stored procedures returning result sets. The IRS support in OCI8 will make migration easier.
  • Instead of calling a PL/SQL procedure and then executing a separate SELECT in PHP, complex processing can be performed in a stored PL/SQL procedure before that procedure returns results to PHP. This architecture might help system scalability since it reduces the number of calls made to the database.
  • PHP Frameworks typically use a basic oci_fetch_* call, so they will will automatically be able fetch IRS results.

I can't suggest turning every individual SELECT statement into an IRS since this will add the overhead of the PL/SQL-to-SQL calls. However there are legitimate use cases for Implicit Result Sets that open some interesting possibilities.

Comments:

Post a Comment:
Comments are closed for this entry.
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today