I usually write about best practice principles and general issues.

Today I thought I would share a bit of my pain with you on a very specific topic: single quotes and CLOBs, and specifically on Oracle 10.2.0.3.

I figure that I lost an hour or two of my life to this one, and would like to pass on the warning to you all.

In Quest Code Tester, we store in put and expected values that you provide in your test definition as CLOBs. We maintain "internal" and "external" values – the external value is a string that is ready to be concatenated directly into the generated test code.

Thus, if the input value is one single quote, we save it in the external_value column as

''''

That is, four single quotes – which evaluate to a single quote. Got it so far, right?

When we generate test code, those four single quotes need to be treated as NULL, so we wrote this function to tell us if a CLOB is really to be considered as NULL:

And it worked just fine in all sorts of situations, but then we got a support issue logged by a user. He was getting the wrong test results: Quest Code Tester was showing that he had provided a NULL value when he had instead entered the single quote character!

What the heck was going on?

Well, it took quite a while to narrow things down. The error did not occur on Oracle9i, Oracle11g, on Oracle 10g 10.2.0.1. It only seems to happen on 10.2.0.3.

And after a while of testing and experimenting, I discovered that on this version of Oracle, you get some very strange behavior when performing a test like:

value_in = ''''''

It's easy to see; you can try it yourself. Copy and paste the following block of code into Toad and run it:

You should see this output:

FOUR QUOTES
FOUR QUOTES

And that is wrong, so wrong. It looks like it doesn't matter how many single quote characters you put inside two single quotes; that equality check will always return TRUE.

Ugh.

So I built the following workaround:

And that seems to work reasonably well – though I would much rather NOT have to do this.

Now you know and hopefully you will never run into this issue – but if you do, you have been warned!