December 21, 2004
Oracle Warehouse Builder Transformations
My apologies for another thinly veiled whine dressed up as useful information, but this one is a doozy.
In our ETL processing we want to reject source data if it doesn't conform to certain quality requirements. One of these is that an identifier needs to be a six digit number, even though it's stored in a VARCHAR2 column (don't blame me, it's a standard Oracle Applications table).
We dutifully included a filter operator and decided to apply the WB_IS_NUMBER transformation. Big mistake.
Why? Vecause the standard, packaged, Oracle supplied routine WB_IS_NUMBER returns a boolean result. Which isn't a valid SQL data type in Oracle 9i. And therefore cannot be used in any Oracle Warehouse Builder mapping. Not big. Or clever.
There is, of course, no mention of this in the manual. Or on metalink. Or on any discussion board that I could see. The solution, which will hopefully save other people the two hours we wasted looking for it, was to write our own custom transform to check whether the passed in value is a number of not.
Our version returns zero (true) or one (false) instead of the PL/SQL boolean equivalents. It looks like this;
CREATE OR REPLACE FUNCTION valid_number (p_number IN VARCHAR2) RETURN NUMBER IS
--initialize variables here
l_number NUMBER;
-- main body
BEGIN
-- If this explicit conversion fails an exception will be raised and we return 1, otherwise return 0
l_number := TO_NUMBER(p_number);
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN 1;
END;
Posted by Andy Todd at December 21, 2004 02:04 PM