Skip navigation.

Christopher Jones

Syndicate content
Notes on the Oracle PHP Apache Linux ("OPAL") stack, with bits of Python, Perl and Ruby for good luck
Updated: 4 hours 28 min ago

Installing PHP on Oracle HTTP Server 12c

Thu, 2014-04-24 14:30

OTN have posted an updated guide for installing PHP on Oracle HTTP Server. Check it out!

Using PHP-FPM is a great way to integrate with Oracle Fusion Middleware.

Performance improvement for OCI_RETURN_LOBS in PHP OCI8 2.0.8

Fri, 2014-03-14 16:48

Reducing "round trips" to the database server is important for performance and ultimately for system scalability. A server round-trip is defined as the trip from PHP to the database server and back to PHP.

Reducing round trips makes everything more efficient: PHP is faster, there is less network overhead, the database doesn't have to do a context switch or do any processing for you. Applications have some control over round trips, for example by effectively using prefetching or by using an appropriate execute mode to minimize unneccessary rollbacks at the end of a script.

The bug filer of Bug 66875 noticed that PHP OCI8's implementation itself could reduce round trips if a particular LOB column meta data value was cached for the duration of a query instead of being re-requested for each row of the query result set.

So, now with OCI8 2.0.8, you should see a performance increase if you are doing multi-row queries involving LOB columns returned as OCI_RETURN_LOBS:

$s = oci_parse($c, "select mylobcol from mylobtab");
oci_execute($s);
while (($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_LOBS)) !== false) {
    echo $row['MYLOBCOL'], "\n";
}

The bug filer tested the patch and added his performance improvement benchmark results to the bug report. The benefit in your environment will vary greatly with the network setup, schema, and how many LOB columns are queried. Test it out and let me know how the new version helps you.

There is no immediate change for LOBs fetched with OCI-Lob::read() and OCI-Lob::load(). This would require a more complex patch than I want to apply at this time. Queries that don't use LOBs are not affected in any way by the patch.

OCI8 2.0 is included in the forthcoming PHP 5.6 code base. For PHP 5.2 to PHP 5.5 you can install it from PECL. PHP 5.5 RPMs with PHP OCI8 2.0.8 are available from oss.oracle.com.

Finally, if your LOB queries return multiple rows, you might also like this tip to reduce PHP memory usage.

The Oracle Database Access Group is hiring

Mon, 2014-03-03 18:41

Over the years I've worked very closely with the Oracle Database Access Group and have a lot of respect for them. I'm happy to share that they are hiring C developers.

To apply, go to irecruitment.oracle.com and enter the code IRC2401606 or IRC2403582 (depending where you want to work) in the Keyword search box.

Here is an excerpt from the job posting:

The Database Access group at Oracle is responsible for providing functionally comprehensive, reliable, high performance, secure and highly available access to the Oracle Database from various client drivers, including proprietary, standards-based and open-source drivers. The group works on the high level drivers, the Oracle Call Interface (OCI) layer, the Oracle wire protocol (TTC) and highly scalable server side protocol handlers that together connect an application written in any language securely to the Oracle Database Server to provide full featured access to the Oracle Database.

Some of the listed requirements are:

  • Strong C programming experience.

  • Knowledge and experience with latest application development technologies including open source and web technologies (e.g. PHP, Ruby, Python, Node.js, HTML5, JavaScript)

I believe it when they also say "Work is non-routine and very complex, involving the application of advanced technical/business skills in area of specialization". Check out the postings for all the details. I look forward to working with you.

Addendum: IRC2401606 is also available.

Support for binding Oracle PL/SQL BOOLEAN introduced in PHP OCI8 2.0.7

Tue, 2014-02-11 11:46

I've released PHP OCI8 2.0.7 which has oci_bind_by_name() support for binding PL/SQL's BOOLEAN type to PHP's boolean. The feature is available when PHP OCI8 2.0.7 is linked-with and connects-to Oracle Database 12c. (The necessary Oracle C library support for binding BOOLEAN was introduced in 12c).

Following the existing PHP OCI8 convention, there are two new constants usable with oci_bind_by_name(): SQLT_BOL and OCI_B_BOL. They have identical values. The former mirrors the underlying Oracle library constant's name (yes, it only has one "O"). The latter follows the PHP OCI8 style of having OCI_B_-prefixed names for bind constants. Note the constants can't be used for array binding with oci_bind_array_by_name().

An example usng the new PHP OCI8 2.0.7 feature is:

<?php

/*
  Precreate this PL/SQL function:
    create or replace function is_valid(p in number) return boolean as
    begin
      if (p < 10) then
        return true;
      else
        return false;
      end if;
    end;
    /

*/

$c = oci_connect('hr', 'welcome', 'localhost/pdborcl');

$sql = "begin :r := is_valid(40); end;";
$s = oci_parse($c, $sql);
oci_bind_by_name($s, ':r', $r, -1, SQLT_BOL);  // no need to give length
oci_execute($s);
var_dump($r);                                  // Outputs: bool(false)

?>

Prior to OCI8 2.0.7 you had to write a wrapper PL/SQL block that mapped the PL/SQL true or false values to 1 or 0.

Code without using OCI8 2.0.7:

$sql = "begin if (is_valid(40) = true) then :r := 1; else :r := 0; end if; end;";
$s = oci_parse($c, $sql);
oci_bind_by_name($s, ':r', $r, -1, SQLT_INT);
oci_execute($s);
echo "Result is " . ($r ? "true" : "false") . "\n";  // Outputs: Result is false

The new functionality removes one small pain point and makes your interaction with Oracle Database 12c PL/SQL cleaner and easier.