ODI Series - Quick look at user functions

Today is going to be a quick blog on how to create user functions as it is something I have not covered in the past and they can be quite useful, if you have used user functions then this is probably going to be no use to you and you may as well do something far more interesting instead.

User functions allow you define a customised function that can be used in interfaces or procedures, the function can be used across different technologies. They are useful when you use a manually created function across many interfaces; it gives you the benefit that you don’t have to keep typing all the code into each interface.

They work on the same principle as functions in other applications or programming languages, you pass variables into the function, the variables are used to say calculate a value and in the end a value is passed back from the function to the caller.

A function can be created as global so it is common to all projects or it can be defined at project level.

I am going to go through an example of a user function I had to create recently and go the process of how it was defined, it is nothing complex but I find useful.

Here is the conundrum, I have a standard variable that is a text data value e.g. 01/06/09 (UK date format), and this variable is declared when a scenario is executed. I need to generate a numeric value corresponding to the month of the variable and then use it in a calculation. If the month is April then I need to return 1, if the month is March then I need to return 12. Just to add to that the current month to be used in the function is always the prior month of the month entered in the variable, so if it is May in the variable then I want to use April in the function.

This example is based on using Oracle technology.

Before I created the function I had the following code placed in a mapping column on the target Datastore of the interface



So what it is basically doing is converting ‘01/06/09’ to a date format, it is using the function add_months to get the previous month, it then converts the date back to a string, it then uses the CASE function to convert the month to a numerical value.

As I didn’t want to hard code the date I used a variable to hold the current month.



So it could of ended up like



This is fine but it was a piece of code that I was going to use again and again so the best way to handle this was by using a user function.



In this example I am creating a project based function, it is exactly the same process to create a global variable though it is created by going to the Others tab in the Designer.




The fields are :-

Name: Name of the user function e.g. ReturnMonthVal

Group: Group of the user function. If you type a group name that does not exist, a new group will be created with this group name when the function is saved.

Syntax: Syntax of the user function that will appear in the expression editor; The arguments of the function must be specified in this syntax.

The format of the syntax is

<Function Name>($(Variable),$(Variable2)….)

As I am only passing in one value the format is

ReturnMonthVal($(MonthVar))

It doesn’t have to be called MonthVar it can be named anything that is meaningful.



In the Implementations tab click add, now you can enter the code for the function, so I just pasted the code I had used earlier in the interface mapping, the date had to be updated to the variable name defined in the first Syntax screen.



As this example is using Oracle then it was chosen under the Linked Technologies, if the code was acceptable in its current format for other technologies then they could also be chosen.

If the function was going to be used on different technologies and required different code then this can easily be done by just adding a new implementation, entering the code and applying it to a technology.

To use the function in an interface mapping then it can be achieved with



The objective was to pass a variable to the function so the variable created earlier can be used.



Remember though if you are going to use a variable you will need to declare it say in a package, now the function can be used throughout various integrations without having to rewrite the code each time

So there you have it a quick simple example of how to define and put into practice a user function.