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.

Sunday Nov 25, 2012

How (and when) to move users to mysqli and PDO_MYSQL?

An important discussion on the PHP "internals" development mailing list is taking place. It's one that you should take some note of. It concerns the next step in transitioning PHP applications away from the very old mysql extension and towards adopting the much better mysqli extension or PDO_MYSQL driver for PDO. This would allow the mysql extension to, at some as-yet undetermined time in the future, be removed. Both mysqli and PDO_MYSQL have been around for many years, and have various advantages: http://php.net/manual/en/mysqlinfo.api.choosing.php

The initial RFC for this next step is at https://wiki.php.net/rfc/mysql_deprecation I would expect the RFC to change substantially based on current discussion. The crux of that discussion is the timing of the next step of deprecation. There is also discussion of the carrot approach (showing users the benfits of moving), and stick approach (displaying warnings when the mysql extension is used). As always, there is a lot of guesswork going on as to what MySQL APIs are in current use by PHP applications, how those applications are deployed, and what their upgrade cycle is. This is where you can add your weight to the discussion - and also help by spreading the word to move to mysqli or PDO_MYSQL. An example of such a 'carrot' is the excellent summary at Ulf Wendel's blog: http://blog.ulf-wendel.de/2012/php-mysql-why-to-upgrade-extmysql/

I want to repeat that no time frame for the eventual removal of the mysql extension is set. I expect it to be some years away.

About

Tourists looking out over an Opal mine
I work in the Linux Engineering team at Oracle, espousing the Oracle-PHP-Apache-Linux ("OPAL") stack. I also assist with Python, Perl and Ruby.
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