Oracle and PHP – the deadly mix

WI’ve spent most of the last week getting into, around, and out of the issues related to interoperability of Oracle and PHP.  Before you start laughing, cursing, and blaming, Oracle wasn’t my choice of the database for this specific project.  It’s just the company already had it installed and working for the background, and there needed to be some integration with the front, which is of course MySQL and PHP based.

First thing I do, obviously, is visit to check for the prefix of the functions that I need for Oracle.  Through out my experience with PHP, that’s about the only thing I need to know to start working with the new database.  Oh, and the PHP module installed to provide those functions. Oracle interface for PHP is called is called OCI8.  All you need to do now is install the oci8 module.

Here comes the first trouble.  oci8 is not provided as a pre-compiled package for Fedora Linux.  There is an alternative yum repository – Remi, which has oci8 RPMs, but first of all, the oci8 module is compiled against somewhat outdated Oracle headers (version instead of the latest, and it also needs to replace your native PHP and MySQL packages.  I tried that, and it sort of worked, but I wasn’t happy.  So I got my Fedora packages back and decided that I need to compile oci8 myself.

In order to compile oci8, one needs to download Oracle InstantClient (basic package) and some header files (devel package).  These can be downloaded from the Oracle web site, for free, minus the time for the registration.  The little trick here is that during oci8 compilation process, the includes are searched from locations which do not include the one from Oracle RPM.  I did a simple symlink of the includes folder to where Oracle headers were, and compilation went on just fine.  (Hint: otherwise you’ll get a whole lot of Zend related messages and a fatal error).  Gladly, I only had to do this path correction on the Fedora 9 machine.  My production server with Red Hat Enterprise Linux 5 compiled oci8 without any problems all by itself.

Update: more detailed instructions on the actual installation can be found here and here.

Now that oci8 installed and configured, I spent some time figuring the correct way to specify the DSN.   Oracle uses some weirdly name file (tnsnames.ora) in some weird location, but luckily there is a way to go around it.  More so, I recommend that you remove tnsnames.ora file altogether, since it can add to your troubles.  For example, if you mix spaces and tabs as whitespaces in that file, you are screwed.  So, just get rid of it.  The way you specify DSN is directly in the PHP script, and you use the syntax like so:  “//hostname.or.ip:port/dbname“.  Intuitive, I know.

Once you’ll get connected to the server, you have a whole bag of surprises waiting for you.  That is if you are too used to working with MySQL.  First is the syntax.  Oracle is using PL/SQL, so you wipe the dust of from that really old Pascal textbook that you have somewhere.  “begin :result :=, :param2); end;” – that sort of thing.  Secondly, you’ll be happy to know that prepared queries are supported.  So your workflow will slightly change.  Perl programmers will feel more at home here.  oci_bind_by_name() and oci_execute() are your friends here.  Oh, and while you are at, get familiar with the types of the parameters, because they are important.  And don’t forget that you’ll have to bind each and every variable in the query, or get a fatal error. And since you are learning something here, get ready for the oracle errors.  The most frequent one you’ll get would be something like “Failed to retreive the error message for ORA-12345”, where 12345 would be a number of the error.  So you’ll google for ORA-12345 and ORA-54321 and ORA-XYZZZ a lot.  But than you’ll have a wrapper library and you’ll be OK.

Update: as was noted in the comments, PL/SQL is just an option, not a requirement.  Also, most of the headaches of the above paragraph could be avoided by using one of the PHP frameworks.  I personally haven’t yet tried the framework yet, since I’d like to see things working directly first.  Especially since we are not in the test mode only.

The bigger surprise is still waiting for you though.  You are very likely to discover that OCI8 implementation for PHP is very slow.  And I do mean extremely very slow.  I couldn’t believe that it could be slow, so I went into the source code and OMG!  It is really slow.  The slow part is around fetch_all() against fetch_row().  Basically, it’s always row by row and never all, even if you tell it how many rows you need fetched.

In my case, I have the server a bit far away, and there is a possibility to get many rows back.  So even for a simple query with 140 rows in results I was getting 20 seconds execution time.  Oracle was serving results fast, the network was OK, machines on both sides were powerful and all, but it was still taking 20 seconds or more.

I am still trying to find the solution to this issue, but so far it seems that the current way I do it will be the way to do it.  And the way I do it now is the following.  Never ever run direct SQL queries.  Everything goes through a stored procedure.  The results are returned all in a single row.  And that single row has the BLOB (CLOB actually) with all results in one single XML.  Fetching works good enough to get it, and then parsing is done with one of the billion XML parsers for PHP.

In my case MiniXML worked pretty good until bigger results started coming in.  That’s when I learned an important lesson.  MiniXML parses XML with a regular expression.  PHP has a couple of settings in the configuration file that limits the size of the memory and recursion during regex parsing – pcre.backtrack_limit and pcre.recursion_limit.  If you really want to kill your server, set these to -1 (instead of default 100000) and try a regex against a 1 MB XML file.  Enjoy, cause it won’t be long before everything goes down. I didn’t feel like changing from MiniXML so we just implemented some limits in the queries and stored procedures on the Oracle side, and add a few checks in PHP fail rather than crash the system.

So, to some it up, here is my experience with Oracle and PHP from the last week:

  • I had to register on Oracle web site to download packages
  • I had to re-learn my long forgotten compilation skills
  • I had to go read some C
  • I had to step on the “re-inventing the wheel” path more than once
  • I am parsing XML when working with the database
  • I had a head ache more than twice
  • I didn’t have much fun
  • After all, it works.  Sort of.

One last point in this saga is about Googling.  Ask me any question, and I do mean any question, about MySQL.  Heck, even PostgreSQL.  And the answer is just there, on the first page of Google results.  In any human or programming language.  For any operating system.  You’ll be sorted out and working in less then a minute.   Then, try asking even the simplest of the simplest questions about Oracle and PHP.  Sometimes you’ll find something.  Some other times, you won’t.  The overall feeling I have is that not a lot of people are using Oracle with PHP, and those of them who do are in their majority not very happy.

Now I’ve joined the army.

10 thoughts on “Oracle and PHP – the deadly mix”

  1. I’ve got to say — this is not accurate at all. At my job, I use PHP & Oracle every day, and it’s as quick if not quicker than any other database.

    We are using CodeIgniter as the PHP framework, and there are no restrictions on NEEDING to use PL/SQL. You can run standard SQL against Oracle with no issues, but you have the ability to run PL/SQL as well.

    You should look into using CodeIgniter against Oracle, and I think you might have a different opinion.

    1. im looking for some tutorial for the use of stored procedures on codeigniter… if you have one i will be so in deb with you….

  2. Patrick,

    can you share a bit more info about your setup please? Which version of PHP, oci8, and Oracle are you using? How did you get it running (pre-packaged or compiled), etc.

    As for our setup, an interesting thing I forgot to mention is that any non-PHP implementation is much faster with all other variables being the same (same Oracle, same hosts, same functionality). Also, Oracle itself is pretty fast – we tested the same queries locally and from other interfaces (C++).

  3. You’ll find direct queries are faster than returning REF CURSORs from
    PL/SQL was modelled on ADA.

    For your build problem it sounds like you didn’t pass the Instant Client path name to ‘configure’

    Queries in SQL are generally faster than returning REF CURSORs from PL/SQL packages because they can use prefetching which is all handled in the Oracle libraries under PHP OCI8. The row-by-row you see in OCI8 C code is just fetching from a cache of rows. See oci8.default_prefetch. If your DB is “remote”, than this will have a big performance impact.

    Binding every variable you declare as a bind variable would be logical.

    Your problem with “Failed to retreive the error message” generally only happens if you are using an ORACLE_HOME-style install. Are you sure you are using Instant Client?

    Here’s one reference that may help:

  4. Chris,

    thanks a lot for stopping by and sharing the information. Especially for the book link. After a brief look it seems useful. I’ll read it through in the next few days.

    As for the direct queries – this needs some experimentation and changes on the code that I don’t have a lot of control over, but we’ll try to work something out, even if just to test it. Thanks for the idea. I’ll post more on the progress later.

    The binding issue that I mentioned wasn’t about the binding per se, but rather about specifying variable types. It seems excessive, at least in non-typed programming language, such as PHP.

    About the Instant Client – I’m sure that I built OCI8 with Instant Client, because otherwise I can’t even get it compiled. Is there any way to check if all that I need is in or not? How can I figure out if those Oracle+Zend performance improvements are utilized on my machine?

  5. For numbers, dates and strings you don’t need to specify a type when you bind. Oracle will do a conversion for you. For other types I take the point. I’m sure the interface could be simplified if redesigned. PHP OCI8 was introduced in, what?, PHP 3?

  6. Chris,

    the information you gave me in the previous comment already helped to fix a few things. I will post a follow-up once I am satisfied with the setup.

    Also, I understand that the post might be somewhat offending for all those who put hard labour in development of Oracle, and in PHP integration. I didn’t mean it this way. It was written after a week of frustrating trial and error, so it’s rather emotional.

    As I said, I will post a follow-up once the issues are resolved and my head is clear. :)

  7. Hi Leonid, I am having issues with very poor performance with oci and php yet I see readers here with different experiences – so wondering if I”m doing something unusual. I’m going to change fetch_all to row based as the buffering might an issue.. but if you do ever come back to this topic, I’m certainly interested to hear your collective discoveries

    1. For the project on which I was working on, Oracle was finally abandoned and we stayed with MySQL. I’ve changed jobs a couple of times since then. If I ever get my hands on Oracle again, I’ll let you know how it performs for me. But I doubt that I ever will.

Leave a Comment