MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘xml’ Category

Load XML Local Infile

with 9 comments

Having downloaded and tested some basics of the MySQL 5.5 Release Candidate, I started checking out the new features. While testing the new LOAD XML LOCAL INFILE feature, I discovered that there are restrictions governing the configuration of source XML files.

You must restrict the XML file to a list of tag names that correspond to column names within a tag defined as <row>. The tag names are case sensitive to your column names. You can replace the <row> tag name with any name of your choosing provided you append a clause that maps rows to your substitution XML tag name.

You can’t convert a file that has multiple child XML tags with the same name. Any attempt simply loads the last tag name found in the row hierarchy. Therefore, you should ensure that all source files have a unique list of case-sensitive child tags that map to the column definitions of the import table.

Either of the following table definition provides for lowercase column names. The first one uses nothing to delimit the column names.

, actor CHAR(30) NOT NULL
, part CHAR(20) NOT NULL
, film CHAR(100) NOT NULL

If you run SHOW CREATE TABLE character; you would see the more conventional definition below.

CREATE TABLE `character`
( `role` CHAR(30) NOT NULL
, `actor` CHAR(30) NOT NULL
, `part` CHAR(20) NOT NULL
, `film` CHAR(100) NOT NULL

The following source file format supports the table definition because the XML tags are lowercase names. The source file wouldn’t work if the column names where uppercase or mixed case names.

  <role>Indiana Jones</role>
  <actor>Harrison Ford</actor>
  <film>Indiana Jones and Raiders of the Lost Ark</film>
  <film>Indiana Jones and the Temple of Doom</film>
  <film>Indiana Jones and the Last Crusade</film>

When the XML tags and column names match in case, you can load the file with the following syntax:

LOAD XML LOCAL FILE 'C:/Data/XML/character.html'
INTO TABLE `character`;

If you name the <row> tag <character>, you need to add a clause as noted below.

LOAD XML LOCAL FILE 'C:/Data/XML/character.html'
INTO TABLE `character`
ROWS IDENTIFIED BY '<character>';

If you have two film tags in a row tag, MySQL 5.5 doesn’t raise an error or warning. It simply loads the last <film> value. If you create tables with column names that don’t match on a case-sensitive basis, you’ll receive a 1263 warning message. You can see the warning message with the following command.

SHOW warnings;

The warning message only occurs when a column is not null constrained and the column name fails to match an XML tag attribute in the source file. No error or warning is raised when a column isn’t not null constrained under the same scenario. You can test it and then show warnings.

You should see something like this:

| Level   | Code | Message                                                                         |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Role' at ROW 1   |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Actor' at ROW 1  |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Part' at ROW 1   |
| Warning | 1263 | COLUMN SET TO DEFAULT VALUE; NULL supplied TO NOT NULL COLUMN 'Film' at ROW 1   |

When you define a table with the AUTO_INCREMENT set to a value other than 1, the LOAD XML LOCAL FILE command resets the sequence to 1 before loading. I’d recommend you import into a table without an auto incrementing column and then use the REPLACE INTO to set the surrogate key values of auto incremented columns.

Written by maclochlainn

September 26th, 2010 at 1:56 am

Posted in MySQL,xml

MySQL, XSLT & Xalan Queries

without comments

I posted how to connect to an Oracle database from an XSLT library file back in August 2008. It’s an event driven XML approach that can support web page development. One of my students wanted to do the same thing against MySQL. He quickly saw that it was simply a matter of the switching the JDBC library. He’s got the whole idea bundled on his blog here.

Hope this helps those looking for a solution.

Written by maclochlainn

July 9th, 2010 at 10:55 pm

Posted in Java,MySQL,xml,xslt

Oracle 11g XDB Shake & Bake

with 33 comments

It’s a bit awkward when a post generates a new question, but here’s a quick explanation and example of using XDB (XML Database Server) outside of the realm of APEX. More or less, XDB is an Apache Server equivalent configured inside the database. It’s really a protocol server tied into the Shared Server Oracle*Net Architecture (a correction provided by Marco Gralike). As a note, testing was done by using a NAT static IP addressing for the virtual Windows XP, Vista, and 7 environments.

This blog post will show you how to experiment with the PL/SQL Web Toolkit and build both password protected and unprotected database content. It assumes you have access to the SYS privileged account.

Setting Up a Secure DAD

There’s secure and then there’s secure. This falls in the less than secure category but it does provide a password and uses basic HTTP authentication. The USER is the schema name, and the PASSWORD is the same as that for the SQL*Plus access to the schema.

  1. Connect as the privileged SYS user and run the following script. It creates a generic STUDENT user and grants minimalist privileges, then it creates a DAD (Data Access Descriptor), and authorizes the DAD. Don’t run the command if you’re actively using Oracle APEX on the default configuration of port 8080. It’s there for those folks you are running Tomcat on 8080.
-- This resets the default port so that it doesn't conflict with other environment.
-- This creates the STUDENT Data Access Descriptor.
EXECUTE dbms_epg.create_dad('STUDENT_DAD','/sampledb/*');
-- This authorizes the STUDENT_DAD
EXECUTE dbms_epg.authorize_dad('STUDENT_DAD','STUDENT');
  1. Connect as the STUDENT user and run the following script to create a PL/SQL Web Toolkit procedure.
  -- Set an HTML meta tag and render page.
  owa_util.mime_header('text/html');  -- <META Content-type:text/html>
  htp.htmlopen;                       -- <HTML>
  htp.headopen;                       -- <HEAD>
  htp.htitle('Hello World!');         -- <TITLE>HelloWorld!</TITLE>
  htp.headclose;                      -- </HEAD>
  htp.bodyopen;                       -- <BODY>
  htp.line;                           -- <HR>
  htp.print('Hello ['||USER||']!');   -- Hello [dynamic user_name]!
  htp.line;                           -- <HR>
  htp.bodyclose;                      -- </BODY>
  htp.htmlclose;                      -- </HTML>
END HelloWorld;
  1. Open a browser of your choice, and enter the following URL.

You then see (or should see) the following Basic HTTP Authentication dialog box. Enter the STUDENT user as the User Name and the Password for the database account. Then, click the OK button.


Provided you enter the User Name and Password correctly, you should see the following inside the browser’s display panel. The USER name is a system session scope variable, which will always return the owner of the package because its created as a Definers Rights procedure.


You have now successfully configured your Basic HTTP Authentication XDB, which may offer you some possibilities outside of using Oracle APEX.

Setting Up an Unsecured DAD

The trick here is building on what you did by eliminating the authentication. You do this by using the ANONYMOUS account, like Oracle’s APEX does. Well, not quite like it does because APEX provides a very good user authentication model. It allows you to connect to the ANONYMOUS user where you present and validate your credentials.

Since you have to do all the prior steps, these steps are numbered after those above. You start with step #4.

  1. Generally, the XML configuration is missing one key node that allows repository anonymous access. The missing node disallows anonymous login. You can run the code below as the SYS privileged user to open that up if its missing. You can check whether or not it’s missing by running this as the SYS user:
SQL> @?/rdbms/admin/epgstat.SQL

If it returns the following as the last element of the output, you’ll need to run the PL/SQL block below.

| ANONYMOUS access to XDB repository:                               |
|  To allow public access to XDB repository without authentication, |
|  ANONYMOUS access to the repository must be allowed.              |
Allow repository anonymous access?
1 row selected.

When you run this script, make sure you’re the privileged SYS user. Then, rerun the epgstat.sql script to verify that you’ve enabled anonymous access to the repository. You may also need to refresh your browser cache before retesting it.

  lv_configxml XMLTYPE;
  lv_value     VARCHAR2(5) := 'true'; -- (true/false)
  lv_configxml := DBMS_XDB.cfg_get();
  -- Check for the element.
  IF lv_configxml.existsNode('/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access') = 0 THEN
    -- Add missing element.
    SELECT insertChildXML
           ( lv_configxml
           , '/xdbconfig/sysconfig/protocolconfig/httpconfig'
           , 'allow-repository-anonymous-access'
           , XMLType('<allow-repository-anonymous-access xmlns="">'
       	   || lv_value
       	   || '</allow-repository-anonymous-access>')
       	   , 'xmlns=""')
    INTO   lv_configxml
    FROM   dual;
    DBMS_OUTPUT.put_line('Element inserted.');
    -- Update existing element.
    SELECT updateXML
           ( DBMS_XDB.cfg_get()
           , '/xdbconfig/sysconfig/protocolconfig/httpconfig/allow-repository-anonymous-access/text()'
           , lv_value
           , 'xmlns=""')
    INTO   lv_configxml
    FROM   dual;
    DBMS_OUTPUT.put_line('Element updated.');
  -- Configure the element.
  1. These tasks also require the privileged SYSTEM user account, and you should sign on to that account to run these commands. The first thing you may need to do is unlock the ANONYMOUS account. It is locked by default. After you unlock it, you’ll need to verify no default password was assigned by unassigning a password. The following two commands accomplish those tasks.
-- Unlock the user account.
-- Ensure a password is assigned to the account so you can create a synonym later.
  1. These tasks require the privileged SYS user account because you’re going to create and authorize another DAD.
-- This creates the STUDENT_DB_DAD Data Access Descriptor.
EXECUTE dbms_epg.create_dad('STUDENT_DB_DAD','/db/*');
-- This authorizes the STUDENT_DB_DAD
EXECUTE dbms_epg.authorize_dad('STUDENT_DB_DAD','ANONYMOUS');
-- Open the anonymous account by setting the database-username parameter and value.
EXECUTE dbms_epg.set_dad_attribute('STUDENT_DB_DAD','database-username','ANONYMOUS');
  1. Connect as the STUDENT user and grant EXECUTE permissions on the HELLOWORLD procedure to the ANONYMOUS user account. The GRANT allows you to give unrestricted access to the ANONYMOUS account, which in turn provides it to your web audience.
SQL> GRANT EXECUTE ON helloworld TO anonymous;
  1. Connect as the ANONYMOUS user and create a local synonym that point to the STUDENT.HELLOWORLD procedure. The SYNONYM provides a program name for the URL statement. It’s hides the ownership of the actual procedure by supressing the schema name. (You may need to grant CREATE ANY SYNONYM as the SYSTEM user to the ANONYMOUS user.)
SQL> CREATE SYNONYM helloworld FOR student.helloworld;
After you’ve created the synonym, you want to remove the password from the ANONYMOUS account. The following syntax lets you do that as the privileged SYSTEM user.

  1. Open a browser of your choice, and enter the following URL, which won’t require a User Name or Password.

You should see the same browser panel information as that shown by step #3 above, except one thing. The difference is the user name, which should now be ANONYMOUS. The execution occurs with the permissions of the invoker. This means you’ll see the data you’re allowed to see by the owning schema.

Written by maclochlainn

December 2nd, 2009 at 3:54 am

Quick XML update

without comments

Somebody commented last week that they’d like the source code for an XSL example file found in this older post. I’ve added the requested code there. The code lets you query directly from your XSL against an Oracle database.

I’ve also created a top page for collecting references to XML blog pages and posts. It’s only got a few but it is an area that will be expanded.

Written by maclochlainn

February 17th, 2009 at 12:20 am

Posted in Oracle,xml,XQuery,xslt

Cows don’t fly and LOBs don’t resolve across a DB_LINK

without comments

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:


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:

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:

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:

    (ADDRESS = (PROTOCOL = TCP)(HOST = mclaughlin11g)(PORT = 1521))
      (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:

USING 'orcl';

Now the database link resolves as …

SELECT   item_title
FROM     item@demo_db_link
WHERE    item_id = 1021;

and it returns …

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:

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.

Written by maclochlainn

September 1st, 2008 at 3:07 am

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"
<xsl:output method="html" />
<xsl:template match="/">
        ,'jdbc:oracle:thin:@mclaughlin11g:1521:orcl','plsql','plsql')" />
<xsl:variable name="streaming" select="sql:disableStreamingMode($movies)" />
  select="sql:query($movies,'SELECT item_title, item_subtitle FROM item')" />
<head><title>Oracle Result Set</title></head>
<body style="font-family: sans-serif;">
<table border="1" cellpadding="5">
    <xsl:for-each select="$queryResults/sql/metadata/column-header">
      <th><xsl:value-of select="@column-label" /></th>
  <xsl:apply-templates select="$queryResults/sql/row-set/row" />
<xsl:value-of select="sql:close($movies)" />
<xsl:template match="row">
  <tr><xsl:apply-templates select="col" /></tr>
<xsl:template match="col">
  <td><xsl:value-of select="text()" /></td>

Written by maclochlainn

August 29th, 2008 at 10:56 pm

Posted in Oracle,xml

Tagged with ,

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

Cleaning up wasted LOB space

with 10 comments

After replacing the contents of a BLOB or CLOB column, you will see the size grow because old indexes and segments aren’t deleted or removed from the index. The only way to get rid of the old information is to perform some simple table maintenance. The following provides an example of dumping redundant or obsolete space and indexes.

You should first check space, by using the query provided earlier in my blog to compare LOB indexes and segments. Such extremes as the regular expression in that query aren’t needed when DBAs ensure that LOBs are created with named segments. In this test case, this is the starting point:

TABLE COLUMN                              Segment      Segment
Name  Name      Segment Name              TYPE           Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00007$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00006$$  LOBINDEX      393216
ITEM  ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416

You create a temporary CLOB column in the target table. Then, you update the temporary column with the value from your real column.

ALTER TABLE item ADD (item_temp CLOB);
UPDATE item SET item_temp = item_desc;

When you requery the table’s indexes and segments, you’d find something like the following. You should note the size of the index and segments are three times larger in the real column than the temporary columns.

TABLE COLUMN                              Segment      Segment
Name  Name      Segment Name              TYPE           Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00007$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00006$$  LOBINDEX      393216
ITEM  ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416
ITEM  ITEM_TEMP SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

You drop the real column and add it back, or simply rename the new table to the old column once you’ve dropped it. Then, you update the real column with the values from the temporary column.

ALTER TABLE item ADD (item_desc CLOB);
UPDATE item SET item_desc = item_temp;

You can now requery the table and find that you’ve eliminated extraneous space.

TABLE COLUMN                              Segment     Segment
Name  Name      Segment Name              TYPE          Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00006$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712
ITEM  ITEM_TEMP SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

You drop the temporary column after making the change.


You can now requery the table and find that you’ve eliminated extraneous space.

TABLE COLUMN                              Segment     Segment
Name  Name      Segment Name              TYPE          Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00006$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712