MacLochlainns Weblog

I’ve been revising and correcting typos plus adding new content to that Configuring Mac OS for the Oracle 10g Client blog page. It’s more complete now. It also shows you how to connect from one virtual machine to another by using host file resolution.

Also, I’ve decided that the network configuration steps for VMWare NAT belong in a separate blog page. I’ll try to get that out after Oracle OpenWorld 2008. For those you can’t wait, you’ll find the networking files here:

Mac OS X:

/Library/Application Support/VMWare Fusion/vmnet8

Linux:

/etc/vmware/vmnet8

Windows:

C:\WINDOWS\System32

You’ll find three files. The dhcpd.conf file configures DHCP. The nat.conf file configures incoming ports to your virtual machines, which is known as port forwarding. The mac.conf file stores the physical layer MAC address for your machine.

Oracle OpenWorld 2008 is next week! How time flies since last year and the year before …  Actually, I never attended OpenWorld in the almost 9 years I worked at Oracle. Oddly, I’ve attended each year since leaving.

For virtual acquaintances out there in the cybervoid (courtesy of Neuromancer by Gibson), perhaps we’ll meet at Oracle OpenWorld 2008. If you want to run me down, McGraw-Hill has scheduled me for a book signing Monday afternoon at 2:00 to 2:30 p.m. in the Barnes and Noble store. It is on the second floor of Moscone West. If not, we may pass in the halls, but I’ve posted a photo in my @About page for you if you’re interested in saying hi.

You may also catch up with me at the San Francisco Giant’s game on Tuesday evening. It’s probably a character flaw, but I’m a Giant’s fan for life. I’m still hoping for another World Series appearance in my lifetime.

If you’re there, agitate to dump DUAL! That is, request that Oracle consider making it optional, like MySQL. It’s so embarassing to say MySQL got it right and excuse Oracle for not making the behavior implicit.

Anyway, I look forward to meeting you if you’re there. If you’re not, I’ll post photos like so many others …

The best laid plans of mice and men go awry too easily. I wrote the article on configuring the Oracle Client because it was tiresome to hear how hard it was when it wasn’t. Then, my students tried the bridged networking instructions, which failed on our campus.

Why did they fail? They failed because our wireless network is configured to disallow students to call out to another machine. You might guess that’s to eliminate gaming across the wireless network. This also has the effect of disabling a call to a virtual machine running under a bridged network.

I’ve updated the original post to include instructions for configuring NAT to work. This should help those who work in environments where the router configuration poses access limitations.

You can find it here …

When I wrote my first PHP book, I felt silly explaining the recursive acroynm because PHP could call HTML and HTML could call PHP. I also included the discussion (misnomer or not) on Paul’s Home Page (PHP).

It struck me when I wrote it that Oracle SQL*Plus environment set the pattern for recursvie call environments. It provided the ability to interactively work with the database or call the database through another program unit (whether internal or external).

You’ll find a diagram that hopefully helps you visualize how Oracle implements SQL*Plus, SQL, PL/SQL, and external libraries in this post.

I was working through some questions on the Oracle Technical forum, and thought it might be helpful to post a quick example for reading a CLOB from the database. It’ll produce the following Java Swing application view:

You can find the blog page here …

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