MacLochlainns Weblog

This should have been the blog post, but I forgot to check page first. It went out the wrong way. I’ve moved the content to a static blog page.

You can find the magic to configure your Mac OS X to natively work with a VM instance running on Linux or Windows. It’s 14 steps long.

If you find anything wrong, please post a comment. Also, if you think there are tags that may help somebody that I’ve excluded let me know.

You can find the steps here …

Last week, I was wrapping up an on-site engagement. I couldn’t help but notice that while cows don’t fly, some may appear to fly, as shown in this photo. I took it with my iPhone, in front of the Salt Palace in Salt Lake City, Utah.

I’d been asked a question about whether you can build a view based on a DB_LINK to a LOB. My answer was no but you can write a Java socket and stored procedure to mimic it. Fortunately, they found an external API to solve their immediate problem.

Poking around on the web, there were a few apparently twisted approaches to creating a DB_LINK. There wasn’t a single place where they all had answer, so here’s a stab at it.

Failing to include a USING clause:

It seems that some new folks in the Oracle Community can be challenged by the USING clause in the CREATE DATABASE LINK command syntax. There were a few examples raising ORA-02019 errors. This is the likely syntax that causes it:

CREATE DATABASE LINK demo_db_link
CONNECT TO plsql IDENTIFIED BY plsql;

I was surprised that you could create a database link without an error with this syntax. Shouldn’t it raise an error and prevent giving the impression that it works?

It does raise an error when you try to resolve the database link, as …

SELECT   item_title
FROM     item@demo_db_link
WHERE    item_id = 1021;

This raises the following exception:

FROM    item@demo_db_link
             *
ERROR at line 2:
ORA-02019: connection description FOR remote DATABASE NOT found

The error message is clear if you understand that you can’t create a database link without the USING clause. It appears the reason newbies go down this route is this error:

CREATE DATABASE LINK demo_db_link
CONNECT TO plsql IDENTIFIED BY plsql
USING mclaughlin11g;

This raises the following exception:

USING mclaughlin11g
      *
ERROR at line 3:
ORA-02010: missing host CONNECT string

This error generates because the hostname isn’t a known identifier in SQL. The same error would occur if you substituted a valid TNS Alias name. As the documentation states, the value provided to the USING clause is a string and must be delimited by single quotes.

Reactive Programming:

Oracle’s errors aren’t bad, but then some are misleading without some experience. If a newbie got the prior error, they’d probably figure out that they need to enclose the “host connect string” in single quotes. They might create a database link like this with the machine hostname:

CREATE DATABASE LINK demo_db_link
CONNECT TO plsql IDENTIFIED BY plsql
USING 'mclaughlin11g';

It appears to work because there’s no validation of the link at creation time. Maybe there should be some validation, and it should raise an error when it isn’t found. This would be immediate feedback, and probably save beginners some time. Instead, they’ll get this error when they run it:

SELECT   item_title
FROM     item@demo_db_link
WHERE    item_id = 1021;

This raises the following exception:

FROM    item@demo_db_link
             *
ERROR at line 2:
ORA-12504: TNS:listener was NOT given the SERVICE_NAME IN CONNECT_DATA

Now, this is a definitive error message!!! It tells an experienced Oracle developer to look in the tnsnames.ora file. Hopefully, it leads a newbie to the same file. You probably agree it’s a clear error message.

Putting the pieces together:

Opening the $ORACLE_HOME/network/admin/tnsnames.ora or %ORACLE_HOME%\network\admin\tnsnames.ora file gives us the following orcl service name:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mclaughlin11g)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

The USING clause should point to a SERVICE_NAME value (oops, I forgot to overlay the color on that). You then create the database link with the following syntax:

CREATE DATABASE LINK demo_db_link
CONNECT TO plsql IDENTIFIED BY plsql
USING 'orcl';

Now the database link resolves as …

SELECT   item_title
FROM     item@demo_db_link
WHERE    item_id = 1021;

and it returns …

ITEM_TITLE
----------------------------------------
Harry Potter and the Sorcerer's Stone

Large Objects (LOBs) don’t work across database links:

While the database link works, if we attempt to access a CLOB or BLOB column it fails. This is demonstrated by the next screen shot:

SELECT   item_title
FROM     item@demo_db_link
WHERE    item_id = 1021;

This raises the following exception:

ERROR:
ORA-22992: cannot USE LOB locators selected FROM remote TABLES

Why does it fail? That’s a great question. LOBs are references in the database. They require a thread into the SGA for reading and writing. The thread can only exist in the scope of a transaction. You can’t create a transaction context across a database link. Therefore, a database link does not support a remote thread. You can fix this by writing a socket routine to place a temporary LOB in the local database. I’ll try to get back to this in a week or two and provide an example.

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 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 …

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 ….

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.

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 …

User defined collection datatypes are very useful. If they’ve been created by somebody else, it’s helpful to have a query to find out whether they’re a VARRAY or Nested Table. I actually got tired of hunting for the details. You probably might concur. So, here’s a query to find the collection datatype, the limit of a VARRAY, and the base datatype.

You can find it here …

Moving forward with the external file architecture. The referenced page lets you copy external files from one viretual directory to another. There is Java library with two method. One copies external text and the other image files. Two functions wrap the methods.

The code is posted in this page on the blog.

A while back, I demonstrated how you can read a file directory from SQL. Here’s how you can delete an external file referenced by a BFILE column. There’s only one problem with this level of the architecture, there’s no rollback. However, it does let you delete the external file when you want to delete the column value.

You can find the blog page here …