ORA-22926 when using getClobVal to convert XMLType to CLOB

I ran into a problem the other day when moving some code from one database to another (both XE 11g). The code in question needs to convert an XMLType to a CLOB to do some (hacky) string manipulation on it, and then turn it back to an XMLType.

Here's the original code:

  l_xml := apex_web_service.make_request(...);
 
  -- little hack to remove bad empty namespace from result
  l_clob := l_xml.getClobVal();
  l_clob := replace (l_clob, '<mytag xmlns="">', '<mytag>');
  -- and then strip out bogus namespace to make parsing easier...
  l_clob := replace (l_clob, ' xmlns="http://tempuri.org/"', '');
 
  l_xml := xmltype (l_clob);

The above had been working before, but now failed with "ORA-22926: specified trim length is greater than current LOB value's length".

Googling turned up a forum post which pointed out that getClobVal is deprecated, and the suggestion is to use XMLSerialize instead.

This can only be used in a SQL statement, not as a PL/SQL expression, so I had to rewrite as follows:

select xmlserialize(document l_xml as clob)
into l_clob
from dual;

And the error went away... (It seems some people have had problems even with the xmlserialize function, but that's quite an old post, so that particular bug could have been fixed a long time ago.)

Oracle XE 11g Win64 on Microsoft Azure cloud

(Or: "How to build your own Oracle Cloud in 25 minutes"... ! )

After downloading the recently released Oracle Express Edition for 64-bit Windows, I decided to test it out on a Windows Server 2008 R2 running in the Azure cloud (recently renamed from "Windows Azure" to "Microsoft Azure", I guess because you can run Linux on it, as well).

Here are the high-level steps needed to get this up and running:

1. Sign up for Azure and login to the management portal.
2. Create a new Virtual Machine, select Windows Server 2008 R2. The provisioning of the VM takes around 5 minutes. (I chose to use an A1 instance type, which costs around USD 50 per month, and has 1 core and 1,75GB of memory. Note that XE is limited to use only 1GB of memory, leaving some extra memory for the OS itself and for IIS).
3. When you create the VM, you specify a DNS name for it, for example "myxetest". This becomes the subdomain of Microsoft's "cloudapp.net" domain, for example "myxetest.cloudapp.net". If you want, you can use your own domain name by adding a CNAME record to your domain and pointing it towards the cloudapp.net subdomain.
4. Use Remote Desktop to login to the new server.
5. Download Oracle XE for 64-bit Windows. The file is around 300MB, so this takes less than a minute to download.
6. Unzip the XE file and run setup.exe. Specify a password for the SYS and SYSTEM users, and let the installer do its work. The whole setup process took about 10-15 minutes on my server.
7. Check that the database is running using sqlplus on the command line on the server.
8. Oracle XE 11g comes with Apex 4.0 preinstalled, and running via the Embedded PL/SQL Gateway (DBMS_EPG) on port 8080 (localhost only). You can verify that it's up and running by going to http://127.0.0.1:8080/apex/apex on the server. If you want EPG to work for remote hosts, you have to execute the dbms_xdb.setlistenerlocalaccess procedure and pass FALSE to enable non-local access. (You can also change the port used by EPG via the dbms_xdb.sethttpport procedure.)
9. You need to modify the Windows Firewall settings on the server to allow outbound traffic on port 8080. Finally, you also need to open up port 8080 for the VM in the Azure management portal (you'll find this setting under "Endpoints" for the VM.)

Here's a screenshot after the database has been installed:

The Apex homepage on localhost:

The Apex builder in action, accessed via the default cloudapp.net domain:

A note on production deployments

The Embedded PL/SQL Gateway (DBMS_EPG) is intended for development environments, and is not recommended for production. Since you are running on a Windows server, you probably want to leverage the excellent built-in Internet Information Server (IIS), with the Thoth Gateway which is an open source ASP.NET implementation of the OWA interface which is what the EPG, mod_plsql and the Apex Listener (now called Oracle REST Data Services, or ORDS) use to communicate with the database. You'll need to enable/install IIS first, as this is not enabled by default on a Windows 2008 server. Then download the Thoth Gateway and follow the installation instructions in the downloaded zip file.

Oracle Express Edition (XE) 11g for Windows 64 bit

As announced by Kris Rice (who is the product manager for SQL Developer, Apex Listener and Oracle XE), the Oracle Express Edition (XE) Database version 11g is now (finally!) available for 64-bit Windows.

It's been a looong wait; the 64-bit version for Windows was expected to be released around the same time as the 32-bit version, some 3 years ago.

Anyway, it's here now, which is great! Thanks Kris! :-)

Oracle Express Edition 11g can be downloaded here, it now comes in three flavors (Win 32 bit, Win 64 bit, and Linux 64 bit).

Bad news about Oracle XE 11g

We all love Oracle 10g Express Edition (XE), and I'm sure everyone's waiting for the 11g version which incorporates all the feature enhancements and security fixes from the last three years.

However, it now looks like we have to wait "another year or two" for the 11g version of Oracle Express Edition :-(

Seriously, Oracle? No XE 11g before late 2011? That means something like six years between the 10g and the 11g version?

Meanwhile, Microsoft is releasing its free SQL Server Express Edition on the same schedule as the for-pay version.

If Oracle is serious about using Express Edition to gain converts to the Oracle database, it should seriously reconsider this decision to delay XE 11g.

Leave a comment below if you would like to see Oracle Express Edition 11g before 2011!