The problem with my XML-generating views is that they break down for rows whose XML is longer than 4000 characters.
Meanwhile, the troubles I cited with XMLELEMENT really are limited to SQL*Plus. I was deterred at first, because I want to experiment with unfamiliar features through SQL*Plus first, but when I swallowed that reluctance and remembered that there are other ways to experiment with ad-hoc SQL - TOAD's SQL window, for instance - I was OK.
But then, I wanted to create views that would store the particular combinations of XMLELEMENTs I wanted for various circumstances. Unfortunately, I found that a SELECT query that runs fine can't be used to generate a working view when the result is longer than - you guessed it - 4000 characters.
I'm having a little trouble figuring out where exactly the 4000 character problem kicks in - at first I thought CLOBS were simply converted back to VARCHAR2's whenever I used the concatenation operator |, but that doesn't explain why views fail when the equivalent bare SELECT statements don't.
Anyway, the end of my adventure was that I simply wrote Python scripts to get what I want. I'm beginning to get some nice conveniences built into my personal wrapper for cx_Oracle, and maybe I'll eventually float it around for others' use. There are plenty of tools like SQLObject out there, of course, but as far as I know, they're all focused on OO programmers who hate to handle SQL directly or to think of their data as "rows in a table" rather than "instances of an object". I, on the other hand, think very naturally in SQL statements and relational concepts, and just use some functions to streamline my use of SQL.
Finally, there's one cute little possibility for producing XML that I haven't explored. SQL*Plus can generate an HTML table as output really easily. That could be coupled with a XSLT to produce XML.
4 comments:
I think SQLObject offers more than avoidance of SQL, but that's a huge win, IMO. Of course, SQLObject can also generate the backend SQL statements from Python syntax. Also nice.
As for the rest of your post, I didn't understand one bit of it. :)
Take care.
SQLObject certainly isn't very relational. I still resort to SQL often when doing complex reports (which usually, in SQL, aren't that complex -- but would be in Python). I know there's been some improvements of using more advanced relational queries in SQLObject (I haven't used them myself), but in the end you always have to get back a primary key and a set of fields, and that doesn't cover all relational cases.
That said, SQLObject's SQLBuilder is just a syntax abstraction of SQL, it isn't bound to OO or anything else: http://sqlobject.org/docs/SQLBuilder.html
You might find it useful by itself.
As you're doing the work in Python anyway you could always rely on it's (in my opinion) superior support for XML.
We've found some funny things with the Oracle XML parser on my current project.
If I find myself wanting to dump data from a table into an XML document then I write the queries in SQL, call them using cx_Oracle and then produce the output using Element Tree (http://effbot.org/downloads/#elementtree).
Post a Comment