Just the other day I described how you can use Regular Expressions Functions with Oracle Data Integrator. Well that was only part of the story.  Immediately after that post went out, I had several notes from people who are way smarter than me say “put that in an ODI User Function for reusability”.  This post was to originally be just that, but as I began to work through the example, I ran into an problem.  The ODI User Function was dropping the square brackets I needed for my REGEXP example to work.  This post now serves a dual purpose.  I will definitely show how to put that REGEXP into an ODI User Function, but along the way you will also see how I address the square brackets issue.  This is a known bug and here is a link to it.  Of course you will need to have access to Oracle Support to view it 🙂

Update:Bug 19945159 : Created user function with square Brackets not seen in generated code.

In the previous post we examined the user requirement to display phone numbers in the following format 1 (###)-###-####. To do this we used the REGEXP_REPLACE function in this manner:

REGEXP_REPLACE (phoneNumber, ‘([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})’, ‘1 (\1)-\2-\3′). 

Now lets take this a bit further.  Using a similar mapping configuration, I will show you how to create an ODI User Function to accomplish the same task.

Here’s the new mapping.

Notice I have phone coming in from two different sources.  This will allow me to better demonstrate an issue I found when using the ODI User Function

Now we need to define a User Function for our REGEXP.  Right click on User Function the appropriate Project.  Since we will be passing a value to the function we will define that in the Syntax window.

Next add Oracle as the Implementation technology and enter the Implementation syntax which should be our REGEXP.

Notice what I did here.  The syntax for the REGEXP_REPLACE function has changed.  There are a number of posts that go into the difference between using [:digit:], [0-9], and \d.  None of which I plan to dive into here.  Apparently unlike manually entering in an expression, ODI User Functions do not translate directly into “database code” and instead produce some type of pseudocode that does not like the square bracket.  While doing some research, I found a post that substituted [0-9] with \d.  I tried it here and it worked!

Here a look at the code generated if I copy and paste the REGEXP using the square brackets.  Notice the difference between lines 13 and 15.  All square brackets have been dropped.

Take a look at the code when using \d.  Line 13 matches the code from the working ODI User Function.  In the case above and below, line 15 is there for comparison purposes only.

Now that we have fixed that problem, we need to actually apply this function to our mapping.  Back on the mapping screen, delete the long REGEXP function and replace it with the function defined earlier.  Make note of what is being passed in as a parameter.

Run the mapping and check your output which should look something like

Now that we have a function that will transform a specific Source format into a specific Target format, the EL-T developers no longer need to know how to structure the REGEXP function.  All they need to know is that the Presentation function requires the phone number from the Source system as a parameter and the output will meet the user requirement.  This function is available throughout all the mapping in the entire Project.