MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for August, 2008

Querying Oracle directly from XML

with 7 comments

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>

Written by maclochlainn

August 29th, 2008 at 10:56 pm

Posted in Oracle,xml

Tagged with ,

The FOR UPDATE and WHERE CURRENT OF statements

with 3 comments

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 …

Written by maclochlainn

August 24th, 2008 at 5:47 pm

Synchronizing file deletion with transaction control

without comments

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.

You can find it here ….

Written by maclochlainn

August 22nd, 2008 at 6:46 am

Code for my Oracle Database 11g PL/SQL Programming

with 3 comments

Somebody asked for a URI reference to the PL/SQL code for my Oracle Database 11g PL/SQL ProgrammingOracle Database 11g PL/SQL Programming book. You can find it here on the McGraw-Hill site. If you find any glitches, just update my blog. I’ll fix them and get them reposted.

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.

Written by maclochlainn

August 13th, 2008 at 7:28 pm

Posted in Oracle,xml

Tagged with , , ,

Data Modeling Thoughts about XML Databases

without comments

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 …

Written by maclochlainn

August 13th, 2008 at 7:30 am

Posted in Oracle,xml

Tagged with , ,

XQuery books, tools, and pending new features

without comments

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.

Written by maclochlainn

August 4th, 2008 at 5:26 am

Posted in xml

Tagged with , , ,