Monday Apr 01, 2013

ODI - Tip of the day, Connect/Disconnect call outs

The connect/disconnect call outs are a useful mechanism for executing code at the start and end of connections - you can write SQL, PLSQL and all sorts through this callout. The code is useful for example for recording your own additional audit information. The code is executed using the execute function call via the java driver for your system. This lets you execute arbitrary valid calls for your driver. For Oracle this can include anonymous PLSQL blocks of code which can contain many statements including SQL and PLSQL. You can also use odiRef APIs so you can generate the code to be executed in the context of where it is executed.

To illustrate if I want to record the session ID in my own audit/trace table I can define an on-connect block of code to be executed. You also define which ODI transactions the code is pertinent to. So for example below I have defined an anonymous PLSQL block of code for on connect applicable to transaction 1 - this is the transaction referenced from the KMs I am using, on the right you can see the IKM SQL Control Append is 'Transaction 1'.

Notice how I can use the odiRef calls to get the session id and add it into my block of code. I have also used the commit option to save the details. When an interface or scenario is executed that uses this data server the code will be executed and can be inspected from the ODI operator under the Command On Connect node in the tree;

Again, the code is executed through the java jdbc driver so it must be of the technology that the data server is, but this is a useful exit/call out point for invoking code. You can even use variables within this block to add another level of flexibility.

Thursday Dec 06, 2012

ODI 11g - Cleaning control characters and User Functions

In ODI user functions have a poor name really, they should be user expressions - a way of wrapping common expressions that you may wish to reuse many times - across many different technologies is an added bonus. To illustrate look at the problem of how to remove control characters from text. Users ask these types of questions over all technologies - Microsoft SQL Server, Oracle, DB2 and for many years - how do I clean a string, how do I tokenize a string and so on. After some searching around you will find a few ways of doing this, in Oracle there is a convenient way of using the TRANSLATE and REPLACE functions. So you can convert some text using the following SQL;

  • replace( translate('This is my string'||chr(9)||' which has a control character', chr(3)||chr(4)||chr(5)||chr(9), chr(3) ), chr(3), '' )

If you had many columns to perform this kind of transformation on, in the Oracle database the natural solution you'd go to would be to code this as a PLSQL function since you don't want the code splattered everywhere. Someone tells you that there is another control character that needs added equals a maintenance headache. Coding it as a PLSQL function will incur a context switch between SQL and PLSQL which could prove costly.

In ODI user functions let you capture this expression text and reference it many times across your mappings. This will protect the expression from being copy-pasted by developers and make maintenance much simpler - change the expression definition in one place.

Firstly define a name and a syntax for the user function, I am calling it UF_STRIP_BAD_CHARACTERS and it has one parameter an input string; 

We then can define an implementation for each technology we will use it, I will define Oracle's using the inputString parameter and the TRANSLATE and REPLACE functions with whatever control characters I want to replace;

I can then use this inside mapping expressions in ODI, below I am cleaning the ENAME column - a fabricated example but you get the gist.

 Note when I use the user function the function name remains in the text of the mapping, the actual expression is not substituted until I generate the scenario. If you generate the scenario and export the scenario you can have a peak at the code that is processed in the runtime - below you can see a snippet of my export scenario;

 That's all for now, hopefully a useful snippet of info.


Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality


« April 2014