OWB Interesting Warnings and Errors #1 PL/SQL: ORA-00907: missing right parenthesis

(Please remember you may click on any picture for a larger view!)

I thought while I am writing away on the new Oracle Warehouse Builder 11g Handbook for Oracle Press / McGraw-Hill that I would still put out a few simple and useful gotchas on my blog for beginner and intermediate users. The most interesting things that seem to pop up are the odd and slightly off-target errors you may receive if you do something wrong. So here is interesting warning and error #1. Relatively straight forward, but can still trick any beginner and some intermediate users.

Let us say that we have a situation that calls for using a key lookup operator. Sometimes, when the lookup results in no matching row for the input data, we have the option to tell the operator what we would like to be output (instead of plain old NULL). For example, below, I might not have a match for my incoming data for the column tgt_unit_name. If I do not, I would output a 'default value' and not simply NULL. In the case below is where my error or warning actually arises. Note that I entered 'N/A UNIT' for my default value. If I had entered N/A UNIT without the ' ' marks, this would cause OWB to complain and issue an error that might not make perfect sense to the beginner. Let us now assume that I did NOT enter the opening and closing ' ' tick marks.
If I go ahead and try to validate the mapping that this key lookup operator appeared in, it would validate perfectly. Furthermore, if I decided to generate the code, and give it a once-over, it might look fine as well. However, when i go to my control center to deploy this mapping...the warnings will fire. So the culprit is PL/SQL: ORA-00907: missing right parenthesis. If you have a nice code tool like pl/sql developer, or toad, or sql developer, you'd likely generate this code from your mapping, and throw it into your development environment and look for problems. A beginner might go right ahead and look for some missing parenthesis. Right? Wrong! This is one area in which you have to think about something. OWB creates code. Usually syntactically correct code. It will not normally and purposely create code that has missing parenthesis. The problem is somewhere else, and it deals with the fact that we did NOT put the opening and closing ' ' around N/A UNIT. See below:

We can clearly see the right number of parenthesis are there, but one is not interpreted due to N/A UNIT not being formed correctly! Let's go back to the mapping, and the key lookup operator and make sure we enter 'N/A UNIT'. (Remember, the way you interact with OWB literally is writing code.) We make the change, and deploy the mapping, and get no errors or warnings!

So, what does the working code snippet look like now?
Note that with a nice IDE, like SQL Developer, we can see that the moment the ticks are in place and the 'N/A UNIT' value is handled correctly in the NVL, we have success. This is to illustrate to the beginner or intermediate user that everything you do in OWB is writing code under the hood. Simply forgetting to use the tick marks to form a literal can result in an error that might make you spend many minutes hunting down a missing parenthesis! Remember that OWB generates PL/SQL, but will listen to you, the user (almost too much) and do exactly as you tell it!