Warehouse Builder upgrades

One of my team has just upgraded an OWB implementation on a customers development system. By and large most of the upgrade went well - everything was pretty much as described in the documentation. The upgraded code just needed to be called from a different shell script (we use a third-part cross platform scheduler product to control ten servers), but other that that it was an easy process. The fun started with the second phase of the testing. Modifying existing maps and redeploying code.

We use database constraints to clean our reference data but for fact data we use outer joins to clean reference data. For us, this technique works well. Bad data has NULL records in one or more of the joined columns so we can easily isolate the bad data using a splitter - the good stuff goes to be published and the bad data is moved to a reject table. To help support the reject table also has a reason for rejection column, this is a VARCHAR2(20) which gives us enough space to reject data for more than one reason (say selling a non-existent product to a non-existent customer). The string to insert in the reject code is generated by an OWB expression operator. And this is where our problem occurs for some reason the operator in our map defaults to VARCHAR and not VARCHAR2.Ho-hum, it looks like my team have a bit of investigation to do next week.

I think I'll take a couple of days off!