ODI - Tip of the day, Connect/Disconnect call outs
By David Allan-Oracle on Apr 01, 2013
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.