Matthew Turland

So I started poking around for instructions on installing Oracle XE 10gR2 on my Kubuntu Hardy 64-bit installation recently. I came across this article from Oracle, which seemed like exactly what I wanted. Unfortunately, it assumes that the intended host operating system is 32-bit, which causes issues if you try to install XE through apt as the Oracle article suggests. After following these instructions, I immediately received this error:

W: Failed to fetch http://oss.oracle.com/debian/dists/unstable/Release \ Unable to find expected entry main/binary-amd64/Packages in \ Meta-index file (malformed Release file?)

After that, any apt command issued (related or not) produced this error.

E: The package oracle-xe needs to be reinstalled, but I can't find an \ archive for it.

And finally, when I resorted to using this excellent guide instead, I ran into this problem because of the earlier failed installation.

dpkg: regarding oracle-xe-universal_10.2.0.1-1.1_i386.deb containing \ oracle-xe-universal: oracle-xe-universal conflicts with oracle-xe oracle-xe (version 10.2.0.1-1.0) is present and broken due to failed \ removal or installation.dpkg: error processing oracle-xe-universal_10.2.0.1-1.1_i386.deb \ (--install): conflicting packages - not installing oracle-xe-universalErrors were encountered while processing: oracle-xe-universal_10.2.0.1-1.1_i386.deb

Luckily, I was able to find a solution to purge the failed installation from the system.

dpkg --remove --force-remove-reinstreq oracle-xe-universal

One thing that the Oracle article was useful for was creating a swap file large enough for Oracle to use, as the issue described in the article with not having enough swap space did arise when I initially tried the installation. Beyond that, the non-Oracle guide mentioned earlier worked like a charm and I now have a working XE installation on my system.

Situations involving hierarchical data and relational databases are quite common in web applications. Trees lend themselves quite well to providing organizational structures a web site, such as sitemaps and breadcrumb trails. A slightly less common and different type of situation, where the application is just as useful and a solution is a bit more complex to derive, is one involving graphical data (as in graphs, not graphics) and relational databases. These situations have issues like the shortest path problem and find their solutions in graph theory such as the A* algorithm or Dijkstra’s algorithm. An example of such a situation is an airline web site that requires the ability to locate connecting and round-trip flights and find the flight path with the lowest cost in terms of time or ticket price.

If you use MySQL, this chapter from “Get It Done with MySQL 5″ is a fairly verbose but comprehensive guide to using MySQL to store graphical data. It includes background information such as terminology used in graph theory and has numerous implementation examples of adjacency list graph models, nested set graph models, and breadth-first and depth-first graph search algorithms.

For Oracle users, there’s a slightly more application-oriented tutorial that assumes more theoretical knowledge on the part of the reader. It shows that Oracle’s hierarchical data features unfortunately can’t be used in cases where cycles might exist in graphs (which is handy if you’re trying to detect them) and then goes on to show an implementation that uses temporary tables to store a summary of a graph analysis. A worthy side note is that part 2 of that tutorial deals with a more specialized approach using state machines that may or may not be applicable to your situation.

If you’d like more information on this topic, a good place to look is your local university. Most with a computer science program offer a course in theory of computation, which deals with topics like these as well as context-free grammars in the context of developing programming languages. Even if you never actually use this information to develop a language yourself, it can still serve a good purpose: it make you more informed when engaging in discussions about language development, and it can increase your appreciation for the beauty of a language from a user perspective.

Maggie Nelson posted a blog entry recently with a review of an article entitled “Database Design for PHP Programmers” in the February 2008 edition of php|architect magazine. In it, she remarks on the article being MySQL-oriented and how limited MySQL explain plan support is compared to Oracle. I’ve had some thoughts in my head for a while that are related to these points, so I finally decided to, knock on wood, put pen to paper.

First, I’ll agree with Maggie that Oracle beats MySQL hands down in a number of areas. Explain plans in Oracle are significantly more detailed and informative than they are in MySQL. Oracle offers features to support hierarchical data, whereas the most MySQL can recommend is to use the nested set model. Oracle also fully supports set operators, while MySQL still only supports unions. Oracle offers Oracle XE and Oracle SQL Developer to lower the barrier of entry for new developers. MySQL itself is free, but MySQL only currently offers similar developer-targetted features in the form of four separate tools, one of which (Workbench) is still very much in beta. These are just a few places where Oracle comes out the clear victor against MySQL. From a developer perspective, it’s no contest.

The story doesn’t end there, unfortunately. The major issue with Oracle is licensing, which still costs a pretty penny these days. Oracle is lowering this barrier a little to include midsize companies, but obviously MySQL still comes out more cost-effective. MySQL itself isn’t completely innocent of causing licensing issues, but it has at least remained free and compatible with PHP in terms of licensing up to this point. The LAMP platform became popular with good reason: it gives smaller companies a scalable, zero-cost, effective means of rapidly developing applications. At the moment, the most Oracle can claim is two out of those three points.

In addition to licensing, Oracle also comes with additional administration costs. I’ll use the managed server hosting provider Rackspace, with which I’ve had a fair amount of experience, as an example of this. They provide hosting for some fairly big names, Apple being one of them. Take a look at the database services they offer. They support both MySQL and Oracle. If you look closely, though, you’ll notice this statement on the Oracle page: “Oracle Server Support is not available to our Managed Support Level customers. For information about our Intensive Support Level, please click here.” I think that about sums up my point.

I would actually like to see us switch to Oracle where I work. As a developer, I prefer its feature set, enough so that I’m willing to overlook some of its nastier qualities. However, with the additional costs that would come with licensing and administration by Rackspace (which is still very reasonable for what they provide), I don’t think the cost difference is one that I can justify. Until the licensing and administration requirements that come with it are lessened, I don’t believe Oracle is going to be an ideal solution for me or the average PHP developer.

Additionally, though I’m still a bit skeptical, the Sun buyout of MySQL could accelerate MySQL’s development and in time put it closer to being on par with Oracle’s feature set. Only time will tell on that one, but I’d say it’s just as much of a possibility as Oracle expanding its offerings to include smaller companies. It’s still too early after the buyout to make any remotely definitive predictions as to its long-term effects on MySQL as a product.

But, as Dennis Miller would say, “That’s just my opinion, I could be wrong.” What’s your take?

I’ve been saving up Oracle-related gripes for a little while now. I’ve organized these into categories and, while each category isn’t really enough to stand as a blog entry on its own, all the gripes collectively make for an entry of a decent length. So, here goes.

Oracle 10g Express Edition

A coworker of mine actually came across one of these issues. Specifically, she found out that XE by default does not include the grants necessary to allow the native UTL_FILE package to be used by PL/SQL routines. It would be nice if this was documented somewhere that didn’t require a significant amount of digging to find out.

I managed to come across another one, and apparently an obscure one at that. The development team can’t even figure out how to replicate this one. For no explicable reason, the Enter key stopped working within worksheet tabs. The fix is as simple as going to Accelerators in Preferences, explicitly selecting Default, and clicking OK. This was a really annoying bug to live with until I found out how to fix it.

Oracle Designer

This is the main laundry list of the post.

First off, if you perform a DDL export from a tab other than the DB Admin tab, grants are not included in the resulting deployment script. If you’re deploying a nontrivial number of entities, it makes it annoying to have to switch tabs and go through the deployment process again.

This leads to the next point: all entities have to be reselected each time you want to deploy them. It would be a lot less tedious and annoying to, say, allow for sets of related entities to be created that could be deployed collectively by just selecting the set.

Going back to DDL exports, changes cannot be generated against a database unless you have the privileges necessary to deploy them. I honestly don’t understand why this is, as read privileges are all that are necessary to read the data dictionary tables, which are all that should be required for the test. This limitation doesn’t make the program very conducive to workflow environments.

Again in DDL exports, exported entities are not intelligently ordered in the generated deployment script such that dependencies come before reverse dependencies, which necessitates manual modifications to those deployment scripts and ergo documentation describing these modifications so that the script can be regenerated at a later time and potentially by a different developer.

Can you tell I’m not overly happy with the DDL export?

PL/SQL

I only really have one gripe for this section, but it’s a big one: the first note in the Oracle Application Server mod_plsql User’s Guide, section 3.6 Parameter Passing lies! It may be technically correct, but what it doesn’t mention is that there is a way to return a default expression as opposed to a default value. It’s a bit of a workaround, and it showcases how difficult it can be to work with aggregate data structures in PL/SQL, but it works. Documentation being this lacking is just completely substandard.

Conclusion

I think that about wraps it up. I don’t do straight-up rants that often, but in these cases I felt it was necessary. It’s entirely possible no one at Oracle will ever read them, but at least I can say it wasn’t because they weren’t stated somewhere.

I’ve seen some “interesting” things during my time with database systems, but the one that takes the cake by far is variations in how NULL is interpreted. I’m going to provide some examples to showcase what I’m talking about using Oracle and MySQL, being that my experience is mostly with those two particular systems. Examples given are run on Windows XP SP2 using Oracle Express Edition 10.2.0.1.0 and MySQL Community Edition 5.0.45.

The stated intention of the existence of NULL is to convey the absence of any value. Both Oracle and MySQL say as much (and I particularly like MySQL’s explanation of the reasoning behind this). Oracle, however, immediately goes on to contradict that principle and maintain that a character value with a length of zero is considered to be equivalent to NULL.

Oracle: SELECT CONCAT(‘test’, NULL) FROM dual; -> ‘test’
MySQL: SELECT CONCAT(‘test’, NULL); -> NULL

This behavior manifests itself in some other “interesting” ways.

Oracle: SELECT ‘true’ FROM dual WHERE LENGTH(”) IS NULL; -> ‘true’
MySQL: SELECT LENGTH(”); -> 0

If you’re wondering why I’ve formatted my Oracle query differently in this example, it’s because this particular release of SQL*Plus appears to not want to give me a definitive answer. If I use the equivalent Oracle query, I simply get blank space where I would expect to see NULL. If you invert the WHERE clause in the query, you’ll see what I mean. I wonder why this is?

To make things worse, it is only indirectly noted in the Oracle LENGTH function documentation in the statement that passing NULL to LENGTH will result in NULL. You have to read the section on NULL to find out that the empty string is equivalent to NULL, then put two and two together in order to figure this out. Oracle has made the statement that it is possible this behavior will change in the future. Given backward compatibility-related implications, however, I highly doubt that.

Strings aren’t the only area to which I have an objection with respect to NULL; numbers are, as well. Prior to MySQL 5.0.13, the following example was handled in what I believed was “the right way.” In versions 5.0.13 and later, it was changed to use the same logic as Oracle.

Oracle: SELECT ‘true’ FROM dual WHERE GREATEST(1, NULL) IS NULL; -> ‘true’
MySQL: SELECT GREATEST(1, NULL); -> NULL

What both do now is cause NULL to be the result if any one operand in the expression is NULL. In dealing with a general expression, I can understand this. When that expression deals with a logical set of associated row- or column-wise values, which this and some other functions do, the absence of a value for a single object in the set should not cause this to happen.

MySQL isn’t even consistent about this; in a section on common problems with NULL in its documentation, it’s stated that “Aggregate (summary) functions such as COUNT(), MIN(), and SUM() ignore NULL values.” In another section on working with NULL values, it’s stated that “Two NULL values are regarded as equal in a GROUP BY.” NULL conveys the absence of a value, so how can this be?! Both Oracle and MySQL have a set of functions and operators for dealing with NULL. At least these treat NULL consistently.

In short, I’m not saying NULL shouldn’t exist. I know it has its uses, such as rewriting queries to avoid using subqueries in order to improve efficiency. I’m saying that mainstream database systems should bear in mind the reason for the existence of NULL when deciding how any given function call or expression should handle encounters with it. Until they do, all I can do is recommend caution when dealing with fields that may contain NULL and querying against them. Be cognizant of these fields and operations involving them and make use of your respective database’s functions and operators for handling these cases so as to avoid unexpected results. Don’t let your data be nullified!

Today I continued the trek toward completing the project described in my last entry. Though I don’t think I ran into as many issues today as I did in the past week or so of working on the project, today certainly had it’s fair share.

First up was a rather interesting exception being thrown by a JDBC operation, namely “java.sql.SQLException: SQL string is not Query.” This is apparently intended to be JDBC’s way of explaining that PreparedStatement.executeQuery() doesn’t work for DML operations. To execute one of those, you have to use either execute() or executeUpdate(). Thankfully, a forum thread was able to point me in the right direction on that one.

Next on the list, if Oracle JDeveloper 10.1.3.3.0 tells you “The WAR file is already up to date,” don’t believe it! I don’t know what logic it’s using to decide whether or not the class files constituting a WAR file are out-of-date, but there are definitely some cases where it’s flawed. I spent a better part of the morning trying to figure out why everything from undeploying and redeploying the EAR file to bouncing the OAS installation was still giving me illogical output. Come to find out, I didn’t know the WAR file not being updated was relevant to the problem at the time, but it certainly proved to be in the end! Tried searching for a bug report on this, but came up empty, so maybe it’s just me.

Last but not least, I take issue with the language used in the mod_plsql User’s Guide to describe its process of file upload handling. Though it never explicitly states this, it seems to imply that the internal handling of performing an INSERT operation to place data for an uploaded file into the document table takes place in a separate transaction from that of the action procedure that gets executed afterward.

You have to go to the PL/SQL User’s Guide to read why this is not the case. To sum it up, a transaction can span multiple procedures. A procedure being executed as a data cartridge operates within a transaction that is implicitly committed when that procedure terminates so long as no uncaught exceptions are raised. However, until that point, the effects of any DML operations executed are only visible to the procedure. This includes the INSERT procedure performed by mod_plsql on the document table. What this effectively means is that the only way something other than the procedure can see that the inserted record exists unless the procedure does an explicit COMMIT.

If you read my last post, you know that I was calling a servlet from the data cartridge. You can probably imagine the amount of aggravation this caused me when I ran my servlet locally without issue, had to backtrack to figure out where the servlet was failing when it was deployed, and then found out that a single COMMIT statement at the beginning of my data cartridge procedure made things work as expected. So, yay for lacking Oracle documentation.

I did get the servlet working, though. It can now pull data from the database, convert it from Excel binary to CSV format, and put the converted data back into the database. So, the Clean Content API, while not specifically designed for the purpose for which I’m using it, is at least a somewhat capable solution. That basically sums up my day, folks. I’ll be back on Monday to do it again.

I got assigned an interesting project at work recently. It involved receiving a file upload via PL/SQL. This in itself is relatively trivial and easy to accomplish when running data cartridges on Oracle Application Server via mod_plsql. What was less unremarkable about the nature of the task was that the uploaded file was intended to be a Microsoft Excel binary file containing a single worksheet. Unfortunately, PL/SQL isn’t so divergent in its available native packages that it has readily available functionality to easily handle this situation.

Luckily, my boss had recently visited the annual Oracle OpenWorld conference and while there learned of a new technology of theirs that could help: the Outside In Clean Content API. I’m uncertain as to whether this product came under Oracle’s branding as the result of a merger, buyout, partnership, or what have you. After poking around the net, I saw that it has thus far received very little coverage, presumably because it was a relatively new release.

Clean Content’s primary purpose is to “identify and remove sensitive, confidential or proprietary metadata and hidden information from Microsoft Office documents.” Of course, to be able to accomplish this, it needs to be capable of extracting said data from these document formats. As a side feature of sorts, they expose this functionality in their API, which is available in the form of C++, C#, and Java libraries.

Originally, when I began work on the project, the requirements stated that the uploaded file would be in CSV format. The format requirement was changed later, after I had developed a prototype capable of handling a CSV file. To adapt my existing work to this new requirement, I developed a Java servlet to supplement it, which the data cartridge would call using UTL_HTTP.REQUEST.

This servlet received the name of an uploaded Excel file, used JDBC to pull the binary data from the local database, used the Clean Content API to convert it to CSV format, and used JDBC again to put the converted data back into the same table. It didn’t end up amounting to much in the way of LOC, but it did require some learning on my part.

First off, the Clean Content API is structured in a SAX-like fashion. The best resources to learn it are actually both included in the free download: the Developer’s Guide and the JavaDoc API documentation. Examples in the former show how to restrict the API to analysis only (i.e. not modifying the document data), provide in-memory data to the API (via a ByteBuffer), and how to specify a handler class to intercept events. You may have to peruse several examples to find all this out, but it’s all there if you take the time to read through it (and selectively skip all the parts having to do with document manipulation).

Your handler class has to extend the BaseElementHandler or GenericElementHandler class in the API. I recommend the latter during development, as its start() method can help in the debugging process by showing you what data is being extracted.

The startTextCell() method will indicate when the parser is within a spreadsheet cell containing textual data. However, the TextCellElement it receives contains only coordinate information, not the value of the cell. (Quick note: the coordinate system is 0-based, meaning that the coordinates of the first cell of the spreadsheet are 0, 0.) To actually capture the text, you have to use the text() method. This is a little confusing, but the reason is that it’s possible to encounter textual metadata outside of the spreadsheet cells. A simple class flag property can be used so you know when you are or aren’t within spreadsheet cells when this event occurs.

The startDataCell() method indicates when numeric data is encountered. Something worth mentioning here is that the Excel binary format houses dates as integers. To convert such a number back to its equivalent date, take the date 1/1/1900 and add that number of days to it using GregorianCalendar.add(). An example of this is 39,085, which corresponds to 1/3/2007. You can further format this further by passing the return value of GregorianCalendar.getTime() to SimpleDateFormat.format().

One oddity I ran into during development that was unrelated to the Clean Content API was with the JDBC library. I executed a SELECT query, got back a ResultSet object, and then attempted to call ResultSet.getBytes() to place the value of a BLOB column into a byte array. This was so I could pass that to ByteBuffer.wrap() to be used with the Clean Content API later. However, the returned byte array always came back severely truncated judging by its length and the fact that the Clean Content API could not determine the document type based on it. I wasn’t able to get around to examining the content byte by byte to determine the cause of this, but I did find a solution: ResultSet.getBlob() returns a Blob object and Blob.getBytes() returns the needed (complete) byte array. Apparently Oracle condones this method of obtaining the value, so rather than beat myself up trying to figure out the weirdness that is this situation, I followed the well-beaten path.

Beyond troubleshooting these oddities, along with relearning how to write servlets and learning how to test them in Oracle JDeveloper and deploy them using Oracle Enterprise Manager (and running into this issue in the process), the process of implementing these project requirements was pretty straightforward. Hope my learning experiences end up helping someone else out there. I’m sure there are other existing solutions that could have been applied here, but if nothing else, it showed that there’s more than one way to skin this cat.

OK, I know I promised a post on how NULL in Oracle scares me, but I think I’ll save that for another day. For the moment, I’ve had something else on my mind recently. Someone I know is apparently of the opinion that PHP is “on the way out.” I have to vehemently disagree with this, and not just because PHP is my language of preference.

For starters, there are major corporations that are actively using PHP. Yahoo, current employer of Sara Golemon, is a great example. Facebook, a social networking site whose advertising program threatens Google Adsense enough that they created the Open Social initiative and brought in other companies in order to compete, is another.

While usage of PHP took a slight dip about two years ago, probably due in part to hype growing popularity and advances in other technologies like .NET and Ruby as well as the low adoption rate of PHP 5, its use is back on the rise. The performance improvements and addition of new OOP features are only making PHP a better, more well-rounded solution for the enterprise.

Major corporations are finally getting away from fearing competition from the open source community and are starting to embrace it. Oracle has collaborated with Zend to enable them to produce Zend Core for Oracle. The Oracle Technology Network web site has a dedicated section for PHP Developers as well as a manual and a cookbook. Oracle develops the OCI8 PHP PDO driver and have made fairly recent updates to it to support database resident connection pooling, fast application notification, and other notable 11g features. (Check out Chris Jones’ blog for more info on that project.) Oracle is also beginning to release a substantial number of projects, particularly developer tools, as open source software.

Oracle isn’t the only one, either. Microsoft has even started getting into the game. A FastCGI add-on is now available for IIS 6, Microsoft’s web server. FastCGI is frequently used when Apache is not being run or running PHP as an Apache module is not an option for other reasons, such as shared hosting services that want to support both PHP 4 and PHP 5 on the same machine, so this is quite the boon for Microsoft shops. Microsoft is getting involved in the production of a new PHP database driver for SQL Server 2005. I can say on personal authority that multiple Microsoft representatives were present at ZendCon 2007 and made a presentation to the conference attendees on that very subject.

So this all seriously begs this question: why are these corporations, some of which have been specifically shown to be opposed to open source, now trying to play nice? Keep your friends close and your enemies closer, anyone? If you can’t beat them, join them? OK, enough cliche anecdotes. I think I’ve made my point here. PHP isn’t going anywhere and it’s certainly not “on the way out.” These companies are putting a significant time and energy into supporting integration with their products by open source software and I don’t think they’d make that investment if PHP’s overall outlook was limited to the short-term.

I don’t believe it was ever the specific intention of the open source movement to compete with large companies and their proprietary products, but merely to fill a gap in software needs perceived by the consumer. As such, I find a particular quote by Sun Tzu, author of “The Art of War,” to be appropriate here: “For to win one hundred victories in one hundred battles is not the acme of skill. To subdue the enemy without fighting is the acme of skill.”

Let me start by saying that, as a relational database management system, I like Oracle. It’s full-featured, mature, and very scalable. However, there are a few small areas where I have to wonder what the developers were thinking. I’ve been working with Oracle for just over a year now. While I’m no self-proclaimed authority on the subject, I thought I’d document my thoughts and see if anyone else shared my mind or had found annoyances in other areas. There may be good design decisions behind these situations, but from a usability perspective, they’re blemishes that make Oracle slightly less than pristine.

Different rules for SQL and PL/SQL when using duplicate placeholders in dynamic SQL

Why is this? You don’t have to specify variable bindings multiple times for the same variable in PL/SQL, but you do in SQL. Why not just remove duplication from both cases?

SQL doesn’t support functions returning BOOLEAN values

See the Note under the RETURN Clause section on the CREATE FUNCTION page. (A rather obscure place to make the note, I might add.) Granted, this is an easily remedied situation by using wrapper functions to return equivalent values using other data types, but it shouldn’t have to be remedied at all. It discourages good practices in selecting return data types for functions and, by proxy, needless complexity. Boolean values can obviously be conceptually understood or we wouldn’t have conditional expressions. Would it really be so hard to support implicit or explicit use of boolean return values as well?

PL/SQL doesn’t support the DECODE function

The DECODE function is essentially the functional equivalent of a CASE expression. Granted, CASE statements are more easily readable, but at the cost of brevity. There are instances where use of a full-blown CASE statement seems unwarranted and DECODE would be a perfect fit, but can’t be used. It makes the claim of “tight integration with SQL” seem a little less accurate.

Rules for handling NULL are F.U.B.A.R.

This one probably deserves a whole post by itself, which I’ll probably get around to writing in the near future. Suffice it to say, if you’ve worked with Oracle before, you probably know what I’m talking about.

No Exclusive OR Operator

See for yourself. The basic operators (AND, OR, and NOT) are there. NAND and NOR are derived easily enough by simply using NOT to negate AND and OR respectively. Lack of an XOR operator requires either writing a userland function to do the job or replicating at least one condition in your SQL query at least once. There are other options like using the bitand function, but they lack the level of readability that an XOR expression would have. I honestly can’t imagine that it would be so difficult to implement, either. The only difference between it and the existing OR operator is that the XOR operator returns false instead of true when both operands evaluate to true.

No LIMIT clause support

Several other databases including MySQL, PostgreSQL, and SQLite have it, but commercial databases like Oracle, Microsoft SQL Server, and IBM DB2 don’t. It’s hard to understand why, because it’s really not so complex a feature. It can even optimize runtime in cases where the desired subset starts at the beginning of the result set. Granted, the same effect can be accomplished for each of these, but the syntax is convoluted and unintuitive. For Oracle in particular, a subquery and use of a dynamically determined pseudocolumn ROWNUM is required. If the original query has a GROUP BY clause, then two subquery layers are required. Whether the underlying engine would process it the same way or not, the simple addition of this clause would make the feature easier and more intuitive to implement for developers.

Wrap Up

Despite these small deficiencies, Oracle is an excellent database. The recent additions of Oracle XE and Oracle SQL Developer also make it very easy to get started as a developer. I recommend the SQL Reference and PL/SQL Reference as beginning references. See the Oracle Technology Network for further references.

When I began commuting to Baton Rouge everyday for my job with METHODS Technology Solutions, I also began attending meetings of the Baton Rouge Oracle User Group. They recently held officer elections and I’m happy to say that I was both nominated and elected to the position of Vice President. Part of my duties will entail helping to locate and attract speakers to give presentations of Oracle-related topics at our meetings. So, if you are such a person or know someone who is, please let them know about us!