In this new post to his blog Chris Jones looks at an option to increase the performance of your PHP/Oracle application even more - converting a REF CURSOR into a piped data set via the PDO_OCI extension.
REF CURSORs are common in Oracle's stored procedural language PL/SQL. They let you pass around a pointer to a set of query results. However in PHP, PDO_OCI doesn't yet allow fetching from them. [...] One workaround, when you can't rewrite the PL/SQL code to do a normal query, is to write a wrapper function that pipes the output.
He includes an example, creating an example myproc() that contains the query to select the last names of all employees in the table. This procedure is put inside of a package so it can be called directly in the SQL statement and the ref cursor can be automatically piped to output.
Chris Jones has pointed out a video of a recent presentation at the Singapore PHP User Group Meeting done by Blair Layton (of Oracle Asia Pacific) on the OCI8 extension and using it to connect PHP.
While most PHP developers start off on the LAMP (Linux, Apache, MySQL, PHP) stack there maybe a need to investigate OPAL (Oracle, PHP, Apache, Linux). This talk will discuss what Oracle offers PHP developers including operating system, database, middleware and tools support. You will find out how to take advantage of specific features in Oracle Database 11g that assist PHP developers such as Database Resident Connection Pooling with code samples and demos. An example architecture from a USA web company of a highly scalable PHP application that uses Oracle database will also be discussed.
His slides are also available for download - Set 1, Set 2 and you can watch the full video here.
On the EasyTech blog, there's a recent post for Zend Framework developers out there working with the Oracle database showing how to execute PL/SQL code from your Zend_Db queries.
Calling PL/SQL code from PHP can be tricky sometimes, specially when the PL/SQL procedure has input and output parameters. In this posting I will show you how to call a procedure from the PHP using Zend Framework. I will assume you have some experience using Zend Framework, specially the Database module (Zend_db).
He walks through the creation of a simple PL/SQL stored procedure and how to prepare your query to get results out of it (Zend_Db_Statement_Oracle and an execute call). There's a few stipulations you'll need to follow - named parameters, reserving space for the output and using references for output variables.
Chris Jones has written up another post about a feature in some of the latest OCI8 libraries for PHP - result caching.
Oracle Database 11g introduced "server-side"and "client-side" result caches. The database cache is enabled with the RESULT_CACHE_MODE database parameter, which has several modes. With the default mode, queries for which you want results to be cached need a hint added - No PHP changes are required.
This caching is perfect for results from smaller lookup tables and can drop CPU usage by quite a bit. Oracle automatically takes care of validating the cache entries when something changes the stored results so your query is always fetching the latest (without having to worry about a timestamp on the cache or anything like that). He gives a few examples of how it would work, comparing the queries both with and without the caching.
On the PHPBuilder.com site today there's a new tutorial continuing in their Oracle theme about using the ADOdb libraries to access the database from your application.
Last week we discussed the use of the OCI8 extension and connection methods for using ADOdb to connect to an Oracle database. This week we'll delve further into the ADOdb library and will disect some example code to get you up to speed!
He gives an overview of some of the functions needed to make the connection and fetch results from a simple query. Some sample code and explanation is also included.
PHPBuilder.com has a new basic tutorial introducing you to the world of the powerful combination of Oracle and PHP:
This article is for the developer who wants to use the PHP scripting language with an Oracle database to develop their application. PHP is one of the most commonly used scripting languages, as it is open source and has procedural as well as object-oriented capabilities. Oracle is a popular RDBMS and that's the reason this combination is very useful for web-based application development.
The article talks about the OCI8 extension and gives a sample of its use to connect to a remote machine (using a full TNSNAMES-formatted string for the host). The code is explained a bit from the connect down to the oci_close.
Christopher Jonespoints out a free event that Oracle is having at its Headquarters (in Redwood Shores, CA) - "Experience Oracle, PHP & Ruby/Rails".
Oracle is running a free workshop at its Headquarters on August 25th. This is a call for developers interested in developing applications in PHP, Ruby/Rails, Java, .Net or even wanting to use Oracle's free SQL Developer tool or learn web development with Oracle's APEX product.
They'll be providing lunch and the information, you just show up and soak it all in. You can find more information about the event here (and register to reserve your spot).
The Oracle Technology Network has a new article posted looking at a basic application that combines the power of an Oracle database with the flexibility of the Symfony framework.
Where should someone who wants to learn Symfony begin? Project Symfony has a very informative and well-kept Web site, with extensive documentation. As it turns out, there is a book about Symfony and there are also numerous tutorials-unfortunately all of them created using a MySQL database. That is, in essence, why I decided to write this article about Symfony and Oracle. It's not a full-blown tutorial, but I hope it will be helpful to those who want to use Symfony with Oracle.
The sample application uses the latest version of Symfony, an Apache web server and the Zend Core for Oracle to install and configure the needed setup. The bulk of the tutorial is taken up with examples of how to tell the ORM layer of Symfony about your database tables and how to build the default classes from them.
Headed to this year's Open World? Want to see more PHP-related talks? Christopher Jonessuggests two talks that you should nominate and check out this year's conference:
Christopher Jones has noted that the latest version of the OCI8 drivers for PHP (in the PECL extension) have been pushed to the current stable package.
HP's OCI8 1.3.3 has support for Oracle's DRCP connection pooling and Fast Application Notification technologies giving it improved scalability and high availability. Overall, the re-architecture of the connection code is more stable. It fixes some obscure edge case issues and lets it handle re-started DB's better. Basic functionality is unchanged.
You can find out more about the package and download this latest edition from its PECL page or check out the whitepaper they recent;y wrote up about PHP and Oracle scalability.
