{"id":11395,"date":"2008-10-25T18:06:51","date_gmt":"2008-10-25T15:06:51","guid":{"rendered":"https:\/\/mamchenkov.net\/wordpress\/?p=11395"},"modified":"2008-10-25T23:45:33","modified_gmt":"2008-10-25T20:45:33","slug":"oracle-and-php-the-deadly-mix","status":"publish","type":"post","link":"https:\/\/mamchenkov.net\/wordpress\/2008\/10\/25\/oracle-and-php-the-deadly-mix\/","title":{"rendered":"Oracle and PHP – the deadly mix"},"content":{"rendered":"\n

WI’ve spent most of the last week getting into, around, and out of the issues related to interoperability of Oracle and PHP.\u00a0 Before you start laughing, cursing, and blaming, Oracle wasn’t my choice of the database for this specific project.\u00a0 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.<\/p>\n

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

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

In order to compile oci8, one needs to download Oracle InstantClient (basic package) and some header files (devel package).\u00a0 These can be downloaded from the Oracle web site<\/a>, for free, minus the time for the registration.\u00a0 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.\u00a0 I did a simple symlink of the includes folder to where Oracle headers were, and compilation went on just fine.\u00a0 (Hint: otherwise you’ll get a whole lot of Zend related messages and a fatal error).\u00a0 Gladly, I only had to do this path correction on the Fedora 9 machine.\u00a0 My production server with Red Hat Enterprise Linux 5 compiled oci8 without any problems all by itself.<\/p>\n

Update:<\/strong> more detailed instructions on the actual installation can be found here<\/a> and here<\/a>.<\/em><\/p>\n

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

Once you’ll get connected to the server, you have a whole bag of surprises waiting for you.\u00a0 That is if you are too used to working with MySQL.\u00a0 First is the syntax.\u00a0 Oracle is using PL\/SQL, so you wipe the dust of from that really old Pascal textbook that you have somewhere.\u00a0 “begin :result := some.procedure.call(:param1, :param2); end;<\/em>” – that sort of thing.\u00a0 Secondly, you’ll be happy to know that prepared queries are supported.\u00a0 So your workflow will slightly change.\u00a0 Perl programmers will feel more at home here.\u00a0 oci_bind_by_name()<\/a> and oci_execute()<\/a> are your friends here.\u00a0 Oh, and while you are at, get familiar with the types of the parameters, because they are important.\u00a0 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.\u00a0 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.\u00a0 So you’ll google for ORA-12345 and ORA-54321 and ORA-XYZZZ a lot.\u00a0 But than you’ll have a wrapper library and you’ll be OK.<\/p>\n

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

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

In my case, I have the server a bit far away, and there is a possibility to get many rows back.\u00a0 So even for a simple query with 140 rows in results I was getting 20 seconds execution time.\u00a0 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.<\/p>\n

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.\u00a0 And the way I do it now is the following.\u00a0 Never ever run direct SQL queries.\u00a0 Everything goes through a stored procedure.\u00a0 The results are returned all in a single row.\u00a0 And that single row has the BLOB (CLOB actually) with all results in one single XML.\u00a0 Fetching works good enough to get it, and then parsing is done with one of the billion XML parsers for PHP.<\/p>\n

In my case MiniXML<\/a> worked pretty good until bigger results started coming in.\u00a0 That’s when I learned an important lesson.\u00a0 MiniXML parses XML with a regular expression.\u00a0 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.\u00a0 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.\u00a0 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.<\/p>\n

So, to some it up, here is my experience with Oracle and PHP from the last week:<\/p>\n