I'm attending the Oracle ISV Tech Forum at the moment and one of the things they're having us do is helping us convert our existing applications from running on whatever it is they're running on at the moment to using Oracle. Unlike almost everyone else I see here, nearly all of our applications are the Linux, Apache, MySQL, and PHP so beloved of open source developers, and here's some of the things I ran into while getting such applications to use Oracle instead of MySQL.
It's not exactly trivial. The first problem I ran into while trying to do this was getting a mod_php that had Oracle support. Yeah, PHP nominally does have Oracle support, but getting it into PHP is actually a fairly difficult task as I very soon found out. The fact that I was using Gentoo and all of the bleeding edge tools (gcc 3.3.4, Apache 2.0.50, and PHP 4.3.9, under Kernel 2.6.7), made this difficult in some ways and easier in others, as one might expect. Fortunately, Gentoo has ebuilds available for the Oracle Instant Client, and those got installed fairly easily enough. Unfortunately, these don't include the header files needed to build PHP with Oracle support, and it took some more googling to dig those up. Even then it didn't quite work. A couple of times the build actually failed and crashed with an inexplicable error, and a couple more times the build went through but didn't bother to build the Oracle support in. I had to make a slight change to one of the OCI header files in order to get the thing to compile, and that left me with the nagging feeling that it might not work (it succeeded though). I had to manually create symbolic links to the Instant Client shared libraries (libclntsh.so.10 to libclntsh.so for instance), in order for the configure script to detect that it was even available. I found myself fiddling with environment variable settings all the time to make sure that everything was working. I do imagine however that rebuilding PHP with Oracle support under Red Hat or Fedora would be much worse. One look at the spec files for the bundled PHP RPM's made me feel that it wasn't worth doing it that way. PHP ought to make things easier by decoupling the extensions from the core, so one doesn't need to rebuild all of PHP just to get Oracle support, and Oracle for its part should be passing around header files for developers to ease this process. DBD::Oracle would also be subject to the same environment variable, symlink, and header file debacle that I found myself in, I suspect. Anyhow, I finally managed to get PHP binaries that had Oracle support, and then came the database conversion. With the Oracle Migration Workbench moving the data from MySQL to Oracle was indeed quite trivial and nearly painless, but there were a number of interesting and unsettling differences between Oracle's and MySQL's implementation of SQL. First thing I noticed is that Oracle doesn't support the LIMIT clause for some reason. This is de rigeur for web-based applications that present paginated table views of data, and looking around revealed that instead of the simple: select email,credit from email limit 0,30 idiom in MySQL, in Oracle you have to make a complex nested subquery that kind of looks like this: select email,credit from (select row_number() over(order by email) linenum, email, credit from email) where linenum between 0 and 30 Ewww... It almost seems as if Oracle's developers gratuitously left out LIMIT just to convince people that subqueries are actually necessary! It looks ugly and inefficient, and I imagine that for tables with many more rows than the toy database I was using it would be very slow. Those of you who have had more experience using Oracle than I have, please show me a better way if there is one... Another thing that tripped me up while doing this conversion was the auto incrementing indexes and the NOW() function that MySQL developers take for granted. All of these had to be done on the client side, which was particularly painful in the latter case. The experience of porting a simple LAMP application to Oracle nicely illustrates the gap between these two worlds. There's a long way to go before the two meet in the middle. *sigh* -- dido "...ang PUMATAY nang dahil sa iyo!" -- Philippine Linux Users' Group (PLUG) Mailing List [EMAIL PROTECTED] (#PLUG @ irc.free.net.ph) Official Website: http://plug.linux.org.ph Searchable Archives: http://marc.free.net.ph . To leave, go to http://lists.q-linux.com/mailman/listinfo/plug . Are you a Linux newbie? To join the newbie list, go to http://lists.q-linux.com/mailman/listinfo/ph-linux-newbie
