Archive for August, 2008
Querying Oracle directly from XML
I’d gone through Doug Tidwell’s XSLT (2nd Edition), I was disappointed that there wasn’t any coverage in Chapter 9 about how to connect through an XSLT Processor to an Oracle database. Overall, the book is improved. I’d recommend it as a reference or a detailed read for the lost truth of XML. Seriously, it’s a very readable book.
I didn’t find anything through a quick search of the web, and I figured a little example might be nice. I used Xalan-Java of the example. Xalan-Java is a convenient open source XSLT Processor, covered in an earlier blog. You can refer to that blog page for the download location and general configuration of Xalan-Java. There is one more configuration step after those in the other blog entry. You need to put the Oracle JDBC driver file in the class path. That’s where the Oracle class files are located. This example uses ojdbc5.jar from an Oracle Database 11g home on Windows:
C:\>SET CLASSPATH=%CLASSPATH%;C:\oracle\11.1.0\db_1\jdbc\lib\ojdbc5.jar |
You can write this sample file, substituting a user, password, table and column (or two) from your own database:
The hidden rows didn’t fit nicely in the other image, but they’re required to make it work. There in the next image. They are the templates for handling columns and rows.
The command line generates an HTML page for you and redirects it to a file:
C:\> java org.apache.xalan.xslt.Process -XSL oracle_xml_query.xsl > sample.htm |
or, the preferred syntax:
C:\> java org.apache.xalan.xslt.Process -XSL oracle_xml_query.xsl -OUT sample.htm |
As per the request, here’s the native code for an XML direct query against an Oracle database.
<?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:sql="org.apache.xalan.lib.sql.XConnection" extension-element-prefixes="sql"> <xsl:output method="html" /> <xsl:template match="/"> <xsl:variable name="movies" select="sql:new('oracle.jdbc.driver.OracleDriver' ,'jdbc:oracle:thin:@mclaughlin11g:1521:orcl','plsql','plsql')" /> <xsl:variable name="streaming" select="sql:disableStreamingMode($movies)" /> <xsl:variable name="queryResults" select="sql:query($movies,'SELECT item_title, item_subtitle FROM item')" /> <html> <head><title>Oracle Result Set</title></head> <body style="font-family: sans-serif;"> <table border="1" cellpadding="5"> <tr> <xsl:for-each select="$queryResults/sql/metadata/column-header"> <th><xsl:value-of select="@column-label" /></th> </xsl:for-each> </tr> <xsl:apply-templates select="$queryResults/sql/row-set/row" /> </table> </body> </html> <xsl:value-of select="sql:close($movies)" /> </xsl:template> <xsl:template match="row"> <tr><xsl:apply-templates select="col" /></tr> </xsl:template> <xsl:template match="col"> <td><xsl:value-of select="text()" /></td> </xsl:template> </xsl:stylesheet> |
The FOR UPDATE and WHERE CURRENT OF statements
The FOR UPDATE clause has been part of Oracle SQL for years. As part of SQLJ, they introduced the WHERE CURRENT OF clause in Oracle 10g. The WHERE CURRENT OF clause allows you to join on ROWIDs the cursor and an UPDATE or DELETE statement in a loop.
For example, you can do something like this:
Alternatively, you can wrap this in a bulk operation (that doesn’t do anything really because it’s the same table), like this:
I have to ask why you don’t simply write a correlated UPDATE or DELETE statement, like this:
UPDATE item i1 SET last_updated_by = 3 , last_update_date = TRUNC(SYSDATE) WHERE EXISTS (SELECT NULL FROM item i2 WHERE i2.item_id BETWEEN 1031 AND 1040 AND i1.ROWID = i2.ROWID); |
There hasn’t been time to run any tuning diagnostics on this but perhaps it should go into the queue of what-ifs. Any thoughts are welcome …
Synchronizing file deletion with transaction control
I finally got back to synchronizing file deletion with transaction control. You’ll need some code from an earlier blog page. The reference to that page is the new page referenced below.
Probably the largest disappointment was discovering the details of global temporary tables. A commit on a DML statement against another table doesn’t trigger a DELETE event on the temporary table. This means a trigger built on a global temporary table doesn’t fire unless you delete from the temporary table. If they did, you could commit a change in one table and trigger another event through the temporary table. That would be a great feature.
Code for my Oracle Database 11g PL/SQL Programming
Somebody asked for a URI reference to the PL/SQL code for my Oracle Database 11g PL/SQL Programming
Also, I’ve updated the Comparative Data Modeling Relational versus XML with screen shots for the code and the mapping of a FLOWR (For, Let, Order by, Where, and Return) statements to nested loops for PL/SQL.
Data Modeling Thoughts about XML Databases
Over the last couple of weeks I had the privilege of reviewing the progression of a presentation made by Michael Bowers at the MarkLogic 2008 User Conference. The presentation makes a compelling case that XML databases may have a much larger market share in the near future.
If you’re interested in my reflection on data modeling and a quick example of highly normalized or structured XML, it’s here …
XQuery books, tools, and pending new features
While working through the semantics of XQuery, the lack of an update feature was annoying. Though there’s now a candidate XQuery Update Facility as of last Friday. Also, Priscilla Walmsley’s XQuery book alerted me to some differences in the handling of style sheets between XPath 2.0 (used in XQuery) and XPath 1.0 (used in XSLT). By the way, I’d recommend her book if you’re writing XQuery and also oXygen as an XML editor. You can also find some great XQuery examples she’s posted here.
Subscribe via RSS