Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

bart (450)

Journal of bart (450)

Wednesday June 25, 2008
06:15 AM

Frustrations about Oracle

[ #36775 ]

I've been a professional user of the Oracle database (now at 10g2) for almost 2 years now. It appears to me to be a very solid and fast database. Its query analysis tools to profile slow SQL queries are excellent. PL/SQL is a nice, "modern" programming language with good features, high speed, and a nice integration of SQL in between procedural programming statements.

Its price does not really bother me... because I don't have to pay for it. I do think that if a company can afford to pay a several professional programmers to work with Oracle full-time, they ought to be able to afford Oracle itself too...

And yet, there are a few things that I find rather frustrating...

Oracle comes with PL/SQL libraries for virtually everything that you can think of: fetching web pages over HTTP, sending mail over SMTP, processing XML with XSLT... But these libraries are not exactly bug free.

Take the XSLT processor as an example. I don't know what it is based on... (probably some open source project, as appears to often be the case with Oracle... :)). It claims to be XSLT 2.0 compatible, yet several basic functions (like lower-case ) are simply not working. disable-output-escaping does not work, and it always indents the output html tree, even when you try to turn it off.

Oracle provides a special way to store XML files, in what it calls "XMLDB". On the surface it's quite impressive... (which is its main purpose, that I can see... :)) You can access it through WebDAV, i.e. using a http URL in a Windows Explorer window, which supports drag and drop to manage the files in it.

And in XSLT, you can access the files in XMLDB using that http URL. But in these files, for example in a document() call, relative URLs to other files simply do not work. You have to use an absolute URL to link to files next to it. That is very impractical, as it requires that you modify the content of the files if you move the repository, or simply, if you add a set of files after you tested them with an XSLT processor on the command line...

What is quite unpleasant, is dealing with VARCHAR2 (strings up to 32k) vs. CLOB (any size) (and their counterparts for binary strings: RAW and BLOB). Those LOBs are a pain to work with: you have to use file-access-like library function calls to work with them, while for VARCHAR2, you can use simple straightforward string manipulation operators just like in other languages. LOBs are just too low level.

And what's really frustrating is that Oracle's PL/SQL libraries virtually all only work with VARCHAR2 and RAW. For example, if you are building a MIME mail message with attachments, there is a library to base64-encode your binary strings at your disposal.... but its parameters and return values are limited to 32k, so you can't use it for larger files than about 24k!

For any real world tasks that goes beyond a simple demo, every single developer has to begin by spending hours writing routines for simple housekeeping tasks, routines that IMO just ought to be part of a standard library. Sure, you can find example code for about any task on the internet, but it's often of dubious quality.

For example, look at the function replaceClob() on http://www.psoug.org/reference/dbms_lob.html (which is, AFAIK, quite a respectable site). It implements an emulation of the core VARCHAR2 function replace(), but for a CLOB. It works by doing the replacements in one chunk at a time, and then simply concatenates the results. Thus, it will skip matching substrings that overlap the edges between chunks. That is quite a serious bug. This is a typical simple example.

So not only do developers have to waste hours of time on housekeeping tasks, the results of their efforts are commonly still of a rather poor quality I doubt that the code I wrote for it is so much better. That is a doubly unpleasant price, for whoever has to pay for it.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.