PHP and Oracle: Christopher Jones

You can now use Oracle's free or paid Linux package channels to install Zend Server.

In three links:

The technical details

Wim Coekaerts's blog gives the background

The press release

Phew! The Oracle OpenWorld conference is: wrapped up. Check me out on video.

Now onto ZendCon down at the other end of Silicon Valley.

My ZendCon talk is on Tuesday afternoon. In what seems just the last few weeks a new version of the Oracle Database (version 11.2) and OCI8 1.4 Alpha have been released. After imparting the secrets of my hard-won, insiders tips on best practices, I have a section on the new changes and interesting things you can (and maybe should) now do. I'll also tell you where we are heading.

Conveniently Packt have just sent me a few copies of PHP Oracle Web Development by Yuli Vasiliev. I'll have one as a door prize at my session. (Another copy of the book and some PHP ElePHPants are headed to the PHP Barcelona conference at the end of the month). Thanks for the books.

Check out the Oracle Magazine Editors' Choice Awards 2009.

Raimonds was at the Oracle OpenWorld Conference last week. He seemed to be everywhere: I bumped into him numerous times and got to see one of his talks. He was always thinking of an interesting problem or great technical challenge to solve.

Congratulations Raimonds!

If you have an interest in dynamic language programming here are the Oracle sessions to attend. There are also unconference sessions happening - check out the OTN area for details on each day.

Conference Sessions

S311373 Agile Web Development: Ruby/Rails and Python/Django with Oracle Database 11g, Sun 10:30-11:30 Hilton Hotel Golden Gate 1
S311371 Best Practices for High-Performance Applications with Oracle Database 11g, Mon 17:30-18:30 Hilton Hotel Golden Gate 1
S311381 Developing & Deploying the Largest Web-sites using PHP and Oracle Database 11g, Tues 16:00-17:00 Hilton Hotel Golden Gate 2
S311643 Oracle Net Services Best Practices for Database Performance and Scalability, Thur 10:30-11:30 Moscone South Room 305

The Best Practice talk covers the impact to the database that your programs have, and what to do about it

The Oracle Net talk will also help the DBA/Developer who needs to get best performance from any system.

Hands-On Lab Sessions

S312331 Develop Painless Enterprise Web Applications using Python/Django and Oracle Database 11g, Sun 13:15-14:15 Hilton Hotel Continental Parlor 1/2/3
S312331 Develop Painless Enterprise Web Applications using PHP and Oracle Database 11g, Sun 14:30-15:30 Hilton Hotel Continental Parlor 1/2/3
S312328 Develop C/C++ Applications with Oracle DB 11g using Oracle Call Interface, Pro*C, ODBC, Mon 14:30-15:30 Hilton Hotel Continental Parlor 1/2/3
S312327 Develop Painless Enterprise Web Applications using Ruby/Rails and Oracle DB 11g, Tues 14:30-15:30 Hilton Hotel Continental Parlor 1/2/3

The Hands-on-Labs are self paced tutorials with the experts on hand to assist and answer questions - directly or indirectly related to the actual content. The C/C++ lab is the practical side the the "Best Practice" talk above. It covers concepts and tools that will help programming in any of the dynamic languages (p.s. in the C/C++ session you choose which of the APIs - OCI/Pro*C/ODBC - you want to learn about: you don't have to do all).

DEMOgrounds

The Dynamic Languages booth is W-006, which is at the front left as you enter the Moscone West exhibition hall. The general hours are:

• Monday 10:30 - 18:30
• Tuesday 10:30 - 18:30
• Wednesday 9:15 - 17:15

I'll do my shifts on Monday morning and all Wednesday - see you there!

I just released an Alpha version of PECL OCI8 1.4 on http://pecl.php.net/package/oci8
The code is also merged to what will eventually be the PHP 5.3.2 and PHP 6.0 releases.

Documentation will appear on http://www.php.net/manual/en/ref.oci8.php
in the next few days, if all goes according to plan.

From the Changelog:

1. Introduce connection attribute functions:

oci_set_module_name
oci_set_action
oci_set_client_info
oci_set_client_identifier

These set values that are visible/used by the database. They
are useful for tracing, authentication and auditing.

2. Introduce connection attribute function:

oci_set_edition

Oracle 11g R2 "editions" allow multiple versions of DB objects
to exist at one time. By setting different editions, two
different versions of an application can run concurrently,
making upgrading easier and faster.

3. Set the DRIVER_NAME attribute of Oracle Database 11gR2
connections to aid application tracing. The value used is
"PHP OCI8" followed by the OCI8 version number. Note the
version number may get truncated in DB views such as
v$session_connect_info.

4. Allow the oci_set_prefetch value to be 0. This is important in
some cases using REF CURSORS in Oracle 11gR2.

5. Introduce OCI_NO_AUTO_COMMIT as an alias for the OCI_DEFAULT
constant (which is not the default value) used by oci_execute().

6. Generate an error if an invalid resource type is used in
oci_bind_by_name

Our Application Server team have put together a new article on installing PHP to use with the latest version of "Oracle HTTP Server" from our Fusion Middleware suite. The article is at: http://www.oracle.com/technology/products/ias/ohs/htdocs/php_ohs.htm.

Hey there! Kuassi Mensah and I will be presenting at the upcoming PHP Code|Works conference tour, which kicks off later this month.

Code|Works is the latest generation of conference from the folk who, amongst other things, present the excellent PHP|Tek conference and publish php|architect magazine.

There are some hot speakers presenting and since each city stop packs so much into the alloted two days, you will get absolutely great value. The pricing is extremely attractive too.

Our Oracle talk is about best practices for using PHP with the Oracle Database and also shows off some of the excellent new features of the recent "Oracle Database 11g Release 2". Full session details are on http://cw.mtacon.com/schedule/talk/oracle . We'll be at the Dallas, Atlanta, Washington and New York venues.

And now for the good news: Oracle readers can get a $50 discount by using the special promo code ENQJ-Q1LV-Z05X at checkout (valid until Sept 15)

If you miss us at Code|Works, the next time to catch us is at Oracle's huge OpenWorld conference in San Francisco (see Kuassi's list of sessions). The following week I'll also be presenting at ZendCon in San Jose, California. A busy time!

Introduction

The Edition-Based Redefinition (EBR) feature of Oracle Database 11gR2 is ideal for web sites that aim for no downtime when releasing application enhancements. Editioning allows certain database objects to have multiple versions all available at the same time. Applications can decide at runtime which version of the objects should be used.

In this example I show how Oracle editioning can be used to upgrade a "live" PHP application. The changes can be made and tested on the production database and then enabled for all users with a one keyword change to the application.

The objects that can be editioned by Oracle are:

• synonyms
• views
• PL/SQL object types:
  • function
  • library
  • package and package body
  • procedure
  • trigger
  • type and type body

Tables themselves can't be editioned but there is support for moving and viewing data across editions.

The Initial PHP Application

To start the example, run SQL*Plus as the user SYSTEM and grant edition access to the application user CJ:

  sqlplus system/systempassword
  grant create any edition to cj;
  alter user cj enable editions;

As the user CJ create a table of employees for the application:

  sqlplus cj/cj
  drop table myemp;
  create table myemp (name varchar2(10), hoursworked number);
  insert into myemp (name, hoursworked) values ('alison', 200);
  insert into myemp (name, hoursworked) values ('kris',   200);
  insert into myemp (name, hoursworked) values ('wenji',  200);
  commit;

Now create the stored function that calculates the number of days of vacation an employee is eligible for. This function is stored in the database so all Oracle applications can reuse the same logic:

  create or replace function 
            vacationdaysleft(p_name in varchar2) return number as
    vdl number;
  begin
   -- For every 40 hours worked, you get 1 day of vacation
   select floor(hoursworked / 40) into vdl 
     from myemp 
     where myemp.name = p_name;
   return vdl;
  end;
  /

Test the function in SQL*Plus by calling:

  select name, vacationdaysleft(name) from myemp;

This returns:

  NAME                           VACATIONDAYSLEFT(NAME)
  ------------------------------ ----------------------
  alison                                              5
  kris                                                5
  wenji                                               5

In PHP OCI8 1.3 the function could be used in an example file, vacation.php:

  <?php

  $c = oci_connect('cj', 'cj', 'localhost/orcl');

  $s = oci_parse($c, 'alter session set edition = ora$base');
  oci_execute($s);

  $s = oci_parse($c, "begin :vdl := vacationdaysleft(:name); end;");
  oci_bind_by_name($s, ":vdl", $vdl, 10);
  oci_bind_by_name($s, ":name", $name, 10);
  $name = 'alison';
  oci_execute($s);

  echo "$name has ".$vdl." days vacation left" . PHP_EOL;

  ?>

This makes a simple call to the stored function and returns in $vdl the number of days vacation for person $name.

Note the ALTER SESSION call. I've added it because I know I'll be using the editioning feature. The 'ora$base' token means to use the root or first edition of objects, i.e. the PL/SQL function vacationdaysleft() we just created. The ALTER statement should appear pretty much immediately after the connection is made - you don't want parts of the application to be using the wrong edition.

The PHP script produces output like:

  $ php vacation.php
  alison has 5 days vacation left

This is good. We put the application into production and employees start using it. All is well for a while.

Enhancing the Application

Now assume the rate used to calculate vacation hours needs to be changed so that now for every 30 hours worked, employees get one day of vacation. Also we want the calculation to include how many days vacation they have already taken.

First, we need a new column to store the vacation they have taken. Adding this column won't affect the running PHP application since it doesn't know about it (this is a good reminder never to do "select *" in an application). The new column is:

  alter table myemp add daysvacationtaken number;

We need to populate that column manually, here with values I've invented:

  update myemp set daysvacationtaken = 2 where name = 'alison';
  update myemp set daysvacationtaken = 3 where name = 'kris';
  update myemp set daysvacationtaken = 0 where name = 'wenji';
  commit;

For more complex scenarios the editioning feature has CROSSEDITION triggers and editioning views to help ensure the appropriate data is available and used correctly in the old and new editions of the application. Remember our current and future enhanced versions of the PL/SQL function can be accessing the same table concurrently - only the PL/SQL code has multiple versions.

Now we need to create the new version of the PL/SQL function. In SQL*Plus create a new edition and set it to be in use for this session:

  sqlplus cj/cj
  create edition e2;
  alter session set edition = e2;

Now create the updated version of the procedure. Because the SQL*Plus session is in edition 'e2' this won't affect the PHP application which runs using edition 'ora$base'. The enhanced function can be created with:

  create or replace function 
            vacationdaysleft(p_name in varchar2) return number as
    vdl number;
  begin
   -- For every 30 hours worked, you get 1 day of vacation
   select floor(hoursworked / 30) - daysvacationtaken into vdl 
     from myemp 
     where myemp.name = p_name;
   return vdl;
  end;
  /

Querying it shows the updated values:

  select name, vacationdaysleft(name) from myemp;

  NAME                           VACATIONDAYSLEFT(NAME)
  ------------------------------ ----------------------
  alison                                              4
  kris                                                3
  wenji                                               6

This is all done while users continue to use the PHP application live and get the original results. You can check that the script still returns the "old" value:

  $ php vacation.php
  alison has 5 days vacation left

Now edit vacation.php and change the ALTER SESSION statement to the new edition:

  $s = oci_parse($c, 'alter session set edition = e2');

When run, the script output now shows the updated calcuation of vacation time:

  $ php vacation.php
  alison has 4 days vacation left

You can see that the roll out of application updates can be made faster and have more thorough testing, increasing the availability and reliability of web sites.

Summary

Oracle Database 11g Release 2 Editioning helps PHP web sites meet the goals of minimal downtime with the frequent upgrade cycle needed by modern web applications. It allows PHP applications, their often complex stored business logic, and large data sets to be updated ready for going live in production without impacting the operation of existing users.

After setting up the edition objects, the simple process of rolling out a new PHP script with the updated edition name allows application upgrades to occur rapidly.

The Oracle manuals have more information about using and managing editions.

Oracle Database 11g Release 2 is now available for download.

It's a great feeling to see this major release available to customers. Lots of hard work has gone into it and my thanks go to all those folk at Oracle who have made it possible.

Currently Linux 32 and 64 bit software is on the Oracle Database Software Downloads page.

There are many new database features. Various whitepapers and the documentation discuss them in detail.

In this post, I want to mention some of the new features useful for PHP OCI8 applications. They include enhancements that let online production applications be upgraded with minimal downtime, and also features improving existing important capabilities introduced in Oracle 11gR1 such as DRCP connection pooling and the Client Query Result Cache.

The new features in Oracle Database 11g Release 2 that I want to highlight are:

• A new Database Resident Connection Pool (DRCP) dynamic performance view V$CPOOL_CONN_INFO. There is a GV$ counterpart for Oracle RAC.

  DRCP is a connection pooling solution that allows web applications to use database server connection resources very efficiently. At the upper end, it lets web applications scale to tens of thousands of connections on commodity hardware.

  The new view displays information about each connection to the DRCP Connection Broker. This gives more insight into client processes that are connected, making it easier to monitor and trace applications that are currently using pooled servers or are idle. (PHP/Apache processes that are idle retain a lightweight connection to the DRCP Connection Broker.)

  Existing DRCP views are unchanged from Oracle 11gR1. (Update: DBA_CPOOL_INFO now has NUM_CBROK and MAXCONN_CBROK columns, equivalent to the pool configuration option parameters of the same names)

• The Oracle 11g Client Query Result Cache (CQRC) is supported with DRCP connections.

  The CQRC is a result cache storing the results of queries in the PHP/Apache process memory. When queries are executed repeatedly, the results can be retrieved directly from the cache, resulting in faster query response time. The results in the cache are automatically marked invalid when data in the database objects being accessed by the query is modified.

• Table Annotations are supported for CQRC, making it easier for existing applications to get performance benefits from result caching. Previously queries had to be modified to include the "/*+ result_cache */" hint. In Oracle Database 11gR2 a DBA can now create or annotate a table as being a candidate for CQRC:

         ALTER TABLE sales RESULT_CACHE (MODE FORCE);  

  The application code does not need to change.

• The prefetching of rows from REF CURSORs (aka Cursor Variables) is supported in Oracle Database 11gR2, greatly increasing performance when Oracle PL/SQL stored procedures and functions are used to encapsulate business logic.

  REF CURSORS are like pointers to result sets. Typically queries are performed in PL/SQL and a REF CURSOR is returned to PHP so the results can be processed.

  Prefetching minimizes database server round-trips by returning batches of rows to an Oracle-managed cache each time a request is made to the database. Prefetching was previously only supported for queries.

  With Oracle Database 11gR2, the default REF CURSOR prefetch row count size is the value of oci8.default_prefetch in php.ini, i.e. 100 in PHP OCI8 1.3. The size can be explicitly changed for a REF CURSOR. For example, to increase the prefetch size of a REF CURSOR to 200:

  	$stid = oci_parse($c, "call myproc(:rc)");
  	$refcur = oci_new_cursor($c);
  	oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
  	oci_execute($stid);
  	oci_set_prefetch($refcur, 200);
  	oci_execute($refcur);
          oci_fetch_all($refcur, $res);
    

  Setting the prefetch count on the "parent" resource $stid does not change the prefetch size for $refcur.

  REF CURSOR prefetching can be used when connected to previous database versions so taking advantage of the new feature can be as simple as relinking PHP with Oracle 11.2 client libraries.

  Prefetching also now works for nested cursor columns:

         select department_name, 
                cursor(select manager_id 
                       from employees
                       where employees.employee_id = departments.department_id)
         from departments;
  

  Nested cursor prefetching requires both the Oracle client libraries linked with PHP and the database to be Oracle Database 11gR2.

Many other new Oracle Database 11gR2 features will benefit web applications. Here are just two of those features:

• Edition-base redefinition makes it safer and easier to do web application upgrades. This feature enables database components of an application to be upgraded and tested while the old version continues to be used. When the changes are complete, an upgraded application using the new database objects can be made available to all users.

  I'll give an example of this in my next blog post since this is exciting stuff.

• RAC One Node has been introduced. This bundling is perfect for sites that need high availability but currently run well with a single database server.

The PHP OCI8 1.2.5 and 1.3 extensions compile and run against Oracle Database 11gR2 without modification. PHP OCI8 1.3 is available with PHP 5.3. It is recommended to upgrade to OCI8 1.3 for PHP 5.2 and earlier versions of PHP. It can be installed from PECL.

PyOhio "The Free Ohio-based Python Miniconference" is on Saturday & Sunday July 25-26 at Ohio State University. On Sunday Catherine Devlin and Oracle Technology Network's Todd Trichler will have an Oracle/Python/Linux Tutorial. This will have something interesting for everyone. Even if you don't want to attend the tutorial fulltime, you can jump in for the bits important to you.

Want to learn more about Python and Oracle, or ask questions about best Python practices - then attend Catherine's Python section and ask questions. Want to know what Oracle database is about to understand its possibilities - then attend the installation part. Want to learn more about Linux and Virtualization - Todd is great on this.

Wish I was going. Send me a postcard!