my recent reads..

Generating CLOB/CDATA elements with XMLDB

Generating CDATA elements with Oracle XMLDB recently got a good airing in the XMLDB forums.

I won't reiterate the discussion there, but offer a summary and some sources.

It seems the current state of affairs is that if you need to generate large text elements with XMLDB you have two options:

  • use DBMS_LOB procedural code to manually construct a CDATA element, or
  • use XMLTYPE views to construct an XML-encoded element

In both cases you need to be careful not to do anything that casts or converts to varchar to avoid the inherent size limitations.

Note that the XML-encoding in XMLTYPE views is automatic, and I currently don't know how to tell it not to encode but rather quote as CDATA.

Some sources and examples:

  • is a Perl script using DBI that demonstrates how to generate an XMLTYPE view over an arbitrary CLOB element, without using XMLSchema. In this case, the CLOB will be automatically XML-encoded [clob-cdata-nonschema.sql is just the plain SQL].
  • clob-cdata-schema.sql shows how you can do a similar thing, but using an XMLSchema definition.
  • clob-cdata-small.sql shows how you can create CDATA elements where the text size is small using the XMLCdata function

read more and comment..

Letting strangers on your Wifi .. need a reason why not?

Sometime back I was hacking my wifi admin pages (to let me register a certain NTP server .. but that's another story), and in the process discovered how broken the security is on my device (an SMC SMC2804WBRP-G Barricade router).

Basically the security check - to make sure you are a valid, logged-in administrator - just redirects to the "action" page which does no further checking of your credentials.

It doesn't take a genius to figure out that if you just post directly to the "action" page you can probably bypass authentication. At least, that's what occured to me, so I tried it and (too my surprise nonetheless) it worked. Or didn't work, depending on your point of view!

To their credit(!), the routine to reset the admin password does require you to send the existing password, but other operations have no barrier.

Here's a simple Perl script that demonstrates how you can "own" an SMC router of this type. It basically lets you reset factory defaults, after which you know the admin password (smcadmin). The factory default has no wifi enabled, so to make any further use of the router you must be connected to a LAN port. But certainly one way to wreck your neighbour's weekend.

I reported this vulnerability to SMC and CERT, but haven't heard whether any action has been taken to fix this.

I also don't know how many other models or brands of routers are susceptible to the same fault. But take this as a warning (and the reason why I am posting this information) ... if you want to offer wifi services to others, make sure your device is not subject to this kind of flaw first!
read more and comment..

Running Instant Client on Linux

I recently had cause to install and configure the Oracle Instant Client under Linux. As I've written before, it is a breeze to get a client up and running.

I did find however that the way the instant client deploys its files can break makefiles and so on if you are doing C/C++ development.

I wrote a simple script (see to install and cleanup an Instant Client and take care of a few things like:

  • move executables into a /bin subdirectory
  • move libraries into a /lib subdirectory
  • create links for commonly know library names
  • create a default network/admin/tnsnames.ora
  • suggest appropriate environment settings for your .bash_profile

Note that the script is written to explicitly handle the basic+sdk+sqlplus installation. If you want to use it for a different combination of kits it will need some simple modification.
read more and comment..

Time to revamp PL/SQL?

Welcome the year of the pig! That maybe appropriate, because I can't help thinking that its 2007 already, and high time that Oracle gave a serious revamp to doddering PL/SQL. Doddering, you say? Well, yes. The past few years have seen incredible language innovation (read Ruby, Python, even JavaScript getting a new AJAXian lease of life) but PL/SQL seems to have been left by the wayside.

I do not know exactly what Oracle have in store for us with 11g, but I sincerely hope it addresses some of my major beefs, which I'd summarise as follows.

1. Give me CLOB-sized VARCHARs
I have a thumping server with a gazillion gigabytes of memory, so why do I spend so much time working around 4000 byte or 32k limits in PL/SQL? Or worse, have my app fail randomly in production when a certain bit of data slips the limit.

These are internal RDBMS implementation details that application programmers should not be concerned with. That's not to say that application programmers shouldn't be concerned about performance, just that they shouldn't be constrained by such arbitrary fundamental restrictions.

OK, so the 4000 byte limit is a SQL thing. But once I am manipulating string data in PL/SQL, if I need a Mb, then please Oracle let me use a Mb.

This is the 21st Century guys. We're not all dealing with simple accounting data. Handling large volumes of text is de rigeur. Text, not nameless objects, be it XML, HTML or just plain ASCII/Unicode.

2. Function-style DBMS_LOB interface
Partly as a result of (1), we often need to resort to DBMS_LOB for dealing with large text. Since it has a largely procedural interface, this usually means we need to drop into PL/SQL when in fact plain SQL would have been preferred.

Rather than deal with temporary LOBs etc, I'd prefer just a function-style interface so most of the LOB handling could be done inline with SQL.

3. Get over the VARCHAR limits with XMLType and XML-related Packages
OK, maybe just a variation on the same theme, but one of the most common situations where application programmers will run into varchar limits is when working with XML. To many of the various XML-related functions and packages are hamstrung by their lack of native support for CLOBs. In many cases, this means what can be elegantly programmed "in the lab" has no practical use because of these limits.

4. Better documentation - proper definitions, real examples
IMHO, most Oracle docs are written according the the "Anne Elk" school of documentation.

It can't get much worse than DBMS_XMLDOM, but let me take an example at random... open the PL/SQL Packages and Types ref and page down a few times. Let's look at DBMS_APPLICATION_INFO.SET_CLIENT_INFO. Parameter is client_info IN VARCHAR2, uhuh. Definition is "Supplies any additional information about the client application". HELLO? Did that actually help anyone who didn't already know what the parameter was for or how it was used?

Normally with most reference guides this is when you turn to the examples to "reverse engineer" the definition. But there are no examples as a rule in the reference docs.

You may find an example in User's Guides, but then again, you may not;) That needs to change.

OK, that's a few for starters. Got any other beefs? Please post a comment, I'd be interested to hear what you have to say.
read more and comment..