Tag: oracle

Categories
work

Oracle in PHP Frameworks

I have been using Zend Framework 1.x at work for some time now. I appreciate the large number of components, many of which my system uses on a daily basis. Yes, it is a large library, but we have a very large application that probably does too many things.

ZF2 is coming out soon and it seems quite different from 1.x. I downloaded the betas and skeleton app, and looked at the well written tutorials, but I am still having a difficult time wrapping my mind around it. So I have decided to see if the grass is greener elsewhere. There are plenty of PHP frameworks out there to choose from, but I have one particular requirement: Oracle support. I work in a somewhat regulated space and the big vendors are preferred over the Open Source databases. And not just any Oracle support, we need to use the oci8 driver.

Most of the new frameworks prefer to use PDO for their database abstraction layer. This is fine for most people who are using MySQL or PostgreSQL. This is not great for us Oracle users. Although there is a pdo_oci extension, it is very buggy. We have to use the oci8 driver instead. And no, I cannot just switch to MySQL.

So, which of the frameworks support oci8? *feel free to correct me in your comments

Framework DBAL oci8 Notes
ZF 1.x Internal  Y
CodeIgniter 2 –  Y
Symfony2 Doctrine  Y  According to the Doctrine 2.0x docs oci8 is supported.
ZF2 Internal/Doctrine  Y
CakePHP 2.x –  + http://www.hassanbakar.com/2012/01/09/using-oracle-in-cakephp-2-0/
Lithium Internal  N MySQL, SQLite3, CouchDB, MongoDB
Aura Wrappers around PDO  N MySQL, PostgreSQL, SQLite3, SQLServer
DooPHP Wrappers around PDO  N
Yii Internal  ?  Found a note from 2009 saying it worked
FuelPHP Internal  ?
Slim – No DB support in framework

When I was researching frameworks 3 years ago I looked at Doctrine as a DBAL and even contributed a few patches to the project, but eventually got frustrated enough to bail on it and go with ZF’s vanilla approach. Should I try out Doctrine again or use something like Slim and keeping all my existing ZF1 database code?

Categories
howto

PHP Oracle [notice] child pid NNNN exit signal Bus error (10)

After some frustration I learned from a forum post that the default password time limit for Oracle 11g is 120 days. Once you get close to that you will go into a grace period, where PHP’s oci connection dies on you with no explanation. You will notice a message like this in you apache log:

  • [notice] child pid NNNN exit signal Bus error (10) in the apache log.

Step 1: Change your password, or rather lets just keep it as it is…

  • ALTER USER myname IDENTIFIED BY mypassword

Step 2: Make us not have to do this again:

  • ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Hope this helps someone.

Categories
howto

Eliminating Mysterious Oracle Indexes

Oracle by default will create cryptic names for indexes unless you specify your own. To get a list of all the indexes currently in use for your schema you can execute:
> select * from user_indexes;
Anything that starts with SYS_ and ends with a $ is an autogenerated index name. It is good to have a common format for your indexes so that any errors are meaningful. address_id_pk is much more readable than SYS_IL0000088969C00006$$. Over here we use the [table]_[column]_[type] format so we can know at a glance where to hunt for the problem.

While doing this I noticed that LOB and CLOB field types automatically generate a SYS_blahblahblah$$ index, polluting your clean design. If that rubs you the wrong way, you can specify the index name for them as well, but the code is a bit messier.