Thursday Jul 25, 2013

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.

Tuesday Mar 13, 2012

PHP 5.4.0 RPMs for 64bit Oracle Linux 5.x are available

I've published some vanilla PHP 5.4.0 RPMs to make new feature testing easier for Oracle Linux 5.x 64 bit users. The standard set of RPMs is at oss.oracle.com/projects/php. The OCI8 extension is also available (this requires the free Oracle Instant Client 11.2 from ULN or OTN.)

Some of the features of PHP 5.4 are:

  • Improved memory usage and performance. Some impressive preliminary reports of the benefits include: http://news.php.net/php.internals/57760 and http://news.php.net/php.internals/57747.

  • File Upload progress support is natively implemented.

  • Support for Traits now allows code reuse:

        trait t1 {
    	function m1() { echo "hi"; }
    	function m2() { echo "bye"; }
        }
    
        class my_c1 {
    	use t1;
    	/*...*/
        }
    
        class my_c2 extends c2 {
    	use t1;
    	/*...*/
        }
    
  • A built-in HTTP server for development is included:

      php -S 127.0.0.1:8888
    
  • Improvements were made to the interactive PHP shell (when PHP is compiled with readline).

  • A shortened array syntax was introduced: $a = [1,2,3];

  • The default character set for several internal functions was changed to UTF-8.

  • Support for multibyte languages is now configurable at run-time instead of compile-time.

  • The value echo tag "<?=" is now always on.

  • Binary number support was added.

  • DTrace support was added.

  • A new typehint indicates a function argument must be callable.

  • Session entropy uses /dev/urandom or /dev/arandom by default for extra security if either is present at compile time.

  • Function call results can now be immediately dereferenced as arrays: foo()[0]

  • Class members can be accessed on instantiation: (new foo)->method()

For more changes see the migration documentation.

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
Links: OTN Node.js Developer Center
OTN PHP Developer Center
Book: Free PHP Oracle book

Search

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