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

More PL/SQL Gateway Goodies

Hot on the heels of version 1.1, which was the topic of my previous blog post, the Thoth Gateway version 1.2 improves on the automatic SOAP Web Service feature, and adds a few new features as well!

Improved Automatic SOAP Web Services

Previously, every function had its own separate service endpoint. This was a bit of a pain, as you would have to (in Visual Studio-speak) add a separate web reference to each function. Now, all functions in a package are grouped together into a single service endpoint. Just add "?wsdl" to the package name, like this:

Upload files to file system instead of database table

Normally, files uploaded via a web page will be stored in the database table specified as "DocumentTableName" in the DAD configuration. In this version, there is a new configuration parameter, "DocumentFilePath", that will cause uploaded files to be saved to the file system instead.

XDB Integration

This version of the Thoth Gateway adds easy integration with Oracle XDB.

Upload files to XDB repository: You can specify a "DocumentXdbPath" in the DAD configuration file that causes uploaded files to be inserted as XDB resources in the specified folder. (This means there are now three different, and mutually exclusive, destinations for uploaded files: database table, file system, and XDB repository.)

Here, for example, we have just uploaded a zip file to XDB, which is then available via SQL, HTTP, FTP and WebDAV as usual:



Download files from XDB repository: You can specify an "XdbAlias" in the DAD configuration file. If this is specified, it will set up a virtual directory (similar to the "PathAlias" parameter) that forwards requests to the XDB repository. You can control which part of the repository you want to expose by specifying the "XdbPathRoot" parameter.

Here we are downloading the zip file via the gateway:



The new options are more fully explained in the installation guide.

Check out version 1.2 of the Thoth Gateway now!

Adventures with Apex, part one: Boosting performance when using DBMS_EPG

We are currently developing a web application using Apex 3.1. There is a handful of developers on the team and we all work in the Apex Application Builder against a shared Oracle 11g database server, which has the Embedded PL/SQL Gateway (DBMS_EPG), rather than a full Apache (Oracle HTTP Server) setup, as per the default database install.

It did not take long before we noticed a significant variation in the time it took to serve Apex pages from the database. Most of the time, pages were served quickly enough, but at uneven intervals pages would just take forever to load. It seemed as if the underlying database or webserver/XDB session was timing out.

After some head-scratching, googling, and experimentation, the following two configuration changes fixed the problem. Page performance is now consistently excellent, and we do not experience timeouts anymore.

1. Increase the XDB session-timeout parameter (I changed it from the default of 6000 [1 minute] to a new value of 30000 [5 minutes].
2. Increase the shared_servers database parameter (I changed it from the default of 1 to a new value of 5).
   

Note: I changed both these settings at the same time, so I did not measure the effect of each individually. I suspect the shared_servers database setting is the most important of the two.

Detailed instructions follow:

Changing the session-timeout parameter in XDB

Connect as SYSDBA and run the following:

declare
  l_newconfig xmltype;
begin
  select updatexml (dbms_xdb.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/httpconfig/session-timeout/text()', 30000)
  into l_newconfig
  from dual;

  dbms_xdb.cfg_update (l_newconfig);

  commit;
end;
/

Note: The database must be restarted for the change to take effect. Issue "alter system register" for the new changes to take effect without restarting the database.

Changing the shared_servers database parameter

Connect as SYSDBA and run the following:

SQL> alter system set shared_servers = 5 scope=both;

System altered.

SQL> alter system register;

System altered.

This will create more background sessions to handle the Apex page requests served through XDB.