Tuesday, February 10, 2015

Hmmm...NOT What I REQUESTED...

The saltier of you out there probably already know this, but it bit me recently so I thought I'd share...

I've used the REQUEST variable to conditionally manage processes. It's quite simple to do and we're actually provided four individual conditions to which the REQUEST variable applies: They are:

  • REQUEST = Expression1
  • REQUEST != Expression1
  • REQUEST is Contained in Expression1
  • REQUEST is NOT Contained in Expression1

Pretty straightforward, right? Not so fast. The problem presented itself when I had two possible REQUEST variable entries...CREATE   and CREATE_ANOTHER. I noticed that the CREATE_ANOTHER code was actually running when I was passing a REQUEST variable of CREATE. The condition on the code was "REQUEST is Contained in Expression1" where Expression1 was CREATE_ANOTHER,FOO,BAR ...or some such.

A trip to the Oracle docs didn't help. It clearly states...

Request is contained within Expression 1
Use this condition to specify a comma delimited list of allowed requests (such as SAVE or UPDATE) in Expression 1. The condition evaluates to true if the value of REQUEST is contained in the list.

I definitely supplied a comma delimited list. What to do, what to do? I found a thread some 5 years ago from Patrick Wolf that provided the answer. He said that "REQUEST is Contained in Expression1"  actually is no more than an INSTR. It does not execute against a list. As a result things began to make sense. I changed my condition to PLSQL Expression with ":REQUEST IN ('CREATE_ANOTHER','FOO','BAR') and life was worth living again.

Created a simple test to demonstrate the findings...

Specifying a REQUEST variable of "New", you can see the second region is rendering albeit incorrectly as "New" was not an element in the comma delimited list. The other two regions displayed as intended.

OK, back on your heads. That's all for now. As I mentioned it may have been something you already knew, but I'd bet money it will continue to bite others out there...at least until Oracle updates the documentation.

...our journey continues.

No comments: