3 Tips For ODI SQL Code Standards

You can write SQL in a variety of places when designing an interface or a procedure with ODI, but a lot of times we forget to take care of somebody. This somebody, is the next person. This person can be anybody, you, friend, colleague or your boss. I had to return and edit my own SQL codes and other’s more than once. I wish that we had some standards from the very beginning. In this post, I will try to keep 3 tips for ODI SQL standards simple enough, to be able to follow them with no real hassle, but also to benefit very greatly for the next time you look at your own code.

Lets start with a test case. Mapping a field in a interface, which will execute the following logic:

  1. If the field is null, insert ‘unknown’.
  2. Replace values of ‘bad’, ‘wrong’ and ’empty’ with ‘unknown-replaced’
  3. Select a replacement for the string from a table.
  4. If no replacement is found, enter the value as is.

Sounds simple enough, lets give it a try with mapping that SQL into an interface column. This is how it looks like without applying the tips on the SQL. I will focus only on the SQL of that specific column and the general SQL generated when executing the interface as an example for my point. Here is the code for the logic above:

case when mytable.myfield is null then ‘unknown’ when mytable.myfield in (‘bad’,’wrong’,’empty’) then ‘unknown-replaced’ else nvl((select reps.replacement from myschema.replacetable reps where reps.origval = mytable.myfield), mytable.myfield) end

Now, lets apply some tips.

1. Line breaks – Just by a simple line break in the right place and the SQL code becomes a lot clearer:

when mytable.myfield is null
then ‘unknown’
when mytable.myfield in (‘bad’,’wrong’,’empty’)
then ‘unknown-replaced’
select reps.replacement
from myschema.replacetable reps
where reps.origval =

2. Tabs – A few tabs here and there, will make the code much more readable:

when mytable.myfield is null
          then ‘unknown’
     when mytable.myfield in (‘bad’,’wrong’,’empty’)
          then ‘unknown-replaced’
                         select reps.replacement
                         from myschema.replacetable reps
                         where reps.origval = mytable.myfield


3. Capitalize the fields, catalogs, schemas, tables and aliases (just like ODI does) – This will make it easy to distinguish where an actual resource is being referenced and where it is pure SQL:

when MYTABLE.MYFIELD is null
          then ‘unknown’
     when MYTABLE.MYFIELD in (‘bad’,’wrong’,’empty’)
          then ‘unknown-replaced’
                         select REPS.REPLACEMENT
                         from MYSCHEMA.REPLACETABLE REPS
                         where REPS.ORIGVAL = MYTABLE.MYFIELD
This was a (really) simple test case. Those tips, should make much easier reading the code after a while, especially, when the SQL Logic is complicated. I hope by reading this post, you will make yours and somebody’s else life a little easier in the future.
You are welcome to share any of your tips for ODI SQL Standards in the comments, thanks.


Oracle Open World 2012

This year, I was able to participate in the Oracle Open World. After a week of walking through session in the Moscone center and some Hotels in San Francisco, I just wanted to share my Oracle Open World experience and a few key things I found interesting. So, fighting laziness and jet leg, here we go.

The first thing I have learned about Open World San Francisco is, that there are a lot of sessions, a lot of rooms and more than one location to go to. So, preparation was in order, this way it is possible to get into the sessions, which interest you the most.  I am not saying you should prepare for a month, just look at the Oracle Open World catalog and choose the most interesting sessions for you. Then, the night before, decide which session you will be attending the next day. At least, this is how it worked for me, since I am an Oracle employee. I am not sure though, if customer or anybody else should register beforehand. One tip I can give you if you are attending the next Oracle Open World conference, try to get a hard copy of the catalog, this will make your life easier.

The next thing I find interesting are the Demo Grounds. Walking around a bit, there are a lot of interesting demos about various Oracle products and many other companies that come there to present their products. Well, the most important tip of the day is… get free stuff :). This is the place to go around, listen to people, scan your badge and get free gadgets and other small, but sometimes cool items. I enjoyed walking around the Demo Grounds, a lot of things to see and people to talk to. For example, I got a chance to talk to two Developers from ODI team about their demo. They presented how to work with ODI and Hadoop, I enjoyed the conversation and learned new things.

The sessions themselves, are about 1 hour and there is a 30 minutes period between each session. This is, probably, for us to be able to move around between different locations and get into our next session in time.

There are a few session I have enjoyed the most. The first is Oracle Data Integrator Product Update and Future Strategy. At which, Denis Gray has talked about current Oracle Data Integrator 11g features and the direction they are headed. One the features I liked, is the ability to run Knowledge Module steps in parallel with the upcoming version of ODI, Oracle Data Integrator 12c.

The second session I have enjoyed is Real-Time Data Integration with Oracle Data Integrator at Raymond James by Tim Garrod and Ryan Fonnett from Raymond James. They presented their solution to integrating journalized data in real time with ODI CDC. Their goal is to capture data and load it with in a 30 seconds time period, which they have successfully implemented. They have talked about the issue that come up when trying to develop such a solution, like parallel executions with CDC, scheduling and load balance, large amount of repository logs and more. They also presented how they have overcame each issue.

There are more sessions I have enjoyed, but I am running out of paper. To finish, I would say, I really enjoyed Oracle Open World and San Francisco, as well. If you are attending the next Oracle Open World, remember three things. Learn new stuff about things that interest you, talk to people and enjoy.




ODI 11g New Features

ODI 11g has introduced a lot of new features and each major release continued to add more and more ODI 11g new features, which accumulated into a lot of features distributed between different releases, starting from ODI, continuing with ODI and finishing, with the latest, ODI new features. For example, in my previous post I talked some about ODI and Java 7.

To tell the truth it is a little annoying reading the new features by going through separate PDFs, each covering one version of ODI 11g new features. If you perform a quick search on Google, you probably will be hitting the different PDFs first, but looking a little further (in my case, second page – from different computers and different logins) you can find an ODI White Paper called “Oracle Data Integrator 11g New Features Overview”, which summarizes the new features of all the releases of ODI 11g until now.

Don’t worry, I am not going to send you searching on Google. Here is the PDF I am talking about:

ODI 11g New Features

Hope this helps to get familiar with the ODI 11g new features, and if I I ever see new ODI White Papers covering more features from newer releases, I will try to keep this post up to date.

Oracle Data Integrator

What is Oracle Data Integrator?
Oracle Data Integrator, also known as Oracle ODI or just ODI, is Data Integration solution from Oracle, based on the ELT approach. For me, it is what I do every day at work, unfortunately, some times at home as well.

ODI is divided into a few main components:

  1. Repository – This where the ODI objects are saved, everything you do in ODI is located at one of the repositories. You can read more about it on a post I wrote a while ago, ODI Repositories.
  2. Agent – This is the server side of Oracle Data Integrator. This is the component, which can be installed anywhere, as long as it have access to the right resource to do the requested job. It can be located on the target database, source or even a dedicated server. Starting from 11g version, it can be deployed both, as standalone or Java EE agent.
  3. GUI – This is the client. It is partitioned into 4 parts: Designer, Operator, Topology and Security Manager. Those, help you design data integration projects, monitor them and control your entire ODI environment.
  4. ODI SDK – 11g version has added a nice addition, ODI SDK, which is a group of classes, that can be used to develop extensions to ODI in order to automate procedures and add flexibility to any part of a project, either in design or run time.

Oracle Data Integrator is a Java based application. It leverages Java as the main technology for connectivity. Using standards like JDBC, JNDI, JMS and more, ODI can connect to many technologies out of the box. This fact, also keeps the door open for ODI to connect to new technologies, as long as the right driver is in place and it supports those standards.

One of the features I like the most about ODI, is the customization level. Almost everything can be customized to meet your goals.  Knowledge modules, ODI Procedures and Open Tools – provide high modification abilites. The main power behind these is, of course, the ODI Substitution methods and Java. Such level of customization is a good thing, but can easily turn into a bad thing, if customized badly, because no planning was done properly. All this, depends on the project goals, size and complication.


ODI and Java 7 support

ODI is out for some time now, and I still did not had a change to share anything about it or even play with it as I should have. The version introduces some really nice new features and it also supports Java 7 (!) on certain platforms, which are listed in the Oracle Fusion Middleware Supported System Configurations.

Well,  maybe the exclamation mark was too much, since I am still to discover what this really means, ODI combined with Java 7. But, let me think for a second…

  1. Will this increase performance?
    This is probably the first question anyone would ask and according to a few interesting articles I have found online, it will. There is the Java 7 performance enhancements in the Java 7 documentation. It is very technical and it is hard to understand, how it will impact Oracle Data Integrator. To give a little perspective, I have found the Performance Benchmark 1.5 1.6 1.7 , which actually gives some numbers and some nice graphs at the bottom.
  2. What about implementing Java 7 in ODI?
    Many developers get to develop different components for ODI in Java, I have discussed some of these, in my previous posts: Knowledge Modules, Procedures or Open Tools and Open Tool Tips. So it is most relevant than ever to mention, that if you have two Oracle Data Integrator environments and the first uses JDK 1.7, what you develop might not run on the second environment running JDK 1.6 , while it probably will vice versa.
  3. Use HotSpot for development and JRockit for production, right?
    There is a common guide line running between weblogic professionals to install development environments with the HotSpot JVM and the production with the JRockit JVM.
    Well… I don’t see how it is going to work out with Java 7, because Java 7 is a best of breed, it is actually a start of an years long process, which will merge between the HotSpot JVM and the JRockit JVM. To simplify, best JRockit features will move to the HotSpot. According to Java 7 Questions & Answers , JRockit is not going to be entitled the Java 7 features. One JVM for both, development and production?

These are my quick thoughts about Java 7 and Oracle data Integrator. To tell the truth, these can probably be applied on ,almost, any other software out there running on Java, especially Oracle Fusion Middleware. But, Oracle Data Integrator running on JDK 1.7 is an interesting thing to try and I will soon do so, more posts will come…

Feel free to say what you think about Java 7 and Oracle Data Integrator in the comments. Thanks.

system errors

Unexpected Java Runtime Environment Errors Starting ODI Agent

Unexpected Java Runtime Environment Error…
These are the kind of errors I like the most. When you see such an error being thrown when starting ODI agent in a production environment, you can go ahead and cancel dinner. Fortunately, for me, this error was on development server, so I had quite a nice dinner that evening.

I wanted to publish this post for some time now, thinking I will get to the cause of it later, my mistake for thinking in the first place. Now, as all the traces of the errors and the log files are beyond my reach, I decided just to share the information. The agent mentioned in this post is ODI 10g agent. Although, I don’t see a reason why such issue cannot arise with at least the 11g standalone agent.

I don’t know exactly what caused these errors, all I know is what I have done to resolve the situation.

The errors:

# An unexpected error has been detected by Java Runtime Environment:
#  SIGSEGV (0xb) at pc=<...>, pid=<...>, tid=<...>
# Java VM: Java HotSpot(TM) 64-Bit Server VM (11.0-b16 mixed mode linux-amd64)
# Problematic frame:
# V  []
## If you would like to submit a bug report, please visit: # # The crash happened outside the Java Virtual Machine in native code. # See problematic frame for where to report the bug.

I have found a nice discussion about the first error on SIGSEGV, libjvm. At the end, it was a hardware failure according to that discussion.

# An unexpected error has been detected by Java Runtime Environment:
#  SIGSEGV (0xb) at pc=<...>, pid=<...>, tid=<...>
# Java VM: Java HotSpot(TM) 64-Bit Server VM (11.0-b16 mixed mode linux-amd64)
# Problematic frame:
# C  []
# If you would like to submit a bug report, please visit:
# # The crash happened outside the Java Virtual Machine in native code. # See problematic frame for where to report the bug.

While searching for the solution, I have seen different sayings about these kind of errors on the web. Starting from hardware failure and finishing with the wind blowing west.

The first error I have solved by asking to move that virtual machine to another physical host, based on the discussion I mentioned earlier. This had changed the error a little, notice that the problematic frame is different in the second error.

Finally, I came across another saying, which claimed that the second error is caused by a jar file being modified, while the application was still using it. Unfortunately, I don’t remember the link.

Well, it was worth a try, I have copied the entire “drivers” and “lib” directories under $ODI_HOME from another identical server. This indeed solved the issue, the agent came right up.
The most natural thing, of course, was to try and reproduce the problem and I tried, but when trying to replace or delete jars in the “drivers” or “lib” directories – nothing happens and restarting the agent works without any problems.

Well to wrap it up, the first error was solved by moving to another physical host, while the second by replacing jar files. The errors came one after another without any period in between. I would say:

  • Definitely, Unexpected errors 🙂
  • Both errors issued the SIGSEGV signal, so it must be a memory related issue, here is what Wikipedia has to say about that: SIGSEGV is the signal sent to a process when it makes an invalid memory reference.
  • Although, I am not sure right now, how the errors were caused, I hope, I have provided some useful information for whoever is expriencing similar problems and if I find out more about any of this, I will update this post.

ocedures, modules, open tools

Knowledge Modules, Procedures or Open Tools

Deciding when to use knowledge module, ODI procedure or an OpenTool is a debate every ODI user will have to face eventually. When facing such a decision, there are some points that make it an easier choice to make.

This post is not written to explain ODI KM, ODI Open Tool, ODI procedure or any technical issues around them. It is more for explaining the concept and when to use each one, as I look at things.

Knowledge module, what is it really?

Actually, despite of what many people might think, knowledge modules do not contain all the functionality one might need for his integration project. They contain common functionality for the technologies which ODI supports out of the box and are templates, which could be learned from and modified in order to meet one’s more specific needs or other technologies. Before starting any project, the assumption that you have all the functionality you need in the KMs should be verified, because if you do – it is nice. If you don’t, it will cost you time and this time should be included in your project schedule, otherwise you will lag behind your timeline. It might happen, that in order to meet the requirements of a project further KM development is required. Although, modifying a KM is not something you do too often, before so, it needs to be assured, that there is no other out of the box KM, which does what is needed, the one we intend to modify is the right one for the job and still does not satisfy. Another thing, usually a KM modification will be needed for features, which we will want to use later on and maybe in other projects as well. As I see it, when modifying a template, you should be creating another template. Modifying KMs all the time, can become a pretty messy thing.

Procedure is like an interface without GUI.

You basically, can achieve almost everything you do in an interface and more by writing procedures, but writing procedures is a much more time consuming procedure :). It is more an object for a single project, you hit a wall and need to add some functionality, which is not built into ODI? “OK”, write a procedure, just don’t be reusing it on right and left, otherwise you might “KO” (excuse me for my geeky humor). Knowledge modules are designed to be reused in different projects while using logical schemas, procedures are not. This would make harder to reuse a procedure for doing the same thing on another logical schema, for this you would need to duplicate the procedure and remember to choose another schema from the drop down menu, such things slow down the development of your projects and are pointless, you know… remembering stuff. Another word on duplication of procedures, I don’t think it is right to maintain two or more objects with the same functionality in one project, just because you need to connect to a different DBs, do you? What if a change will be required? Good luck.
Reusing the procedure with no schema connectivity or within the same connection (for example, locking different tables) is possible and might be fine , as long as the option are used correctly. Although, either way I would not advise to go and distributing it between different projects, if you need a global object, create one. Use either KM or open tool, depending on the task.

Open tool is the way to integrate your own functionality.

I will say right away, that not many are up for such a solution, either because this requires a more deeper java knowledge or the time it might take to develop such a thing is a little longer. This is the right spirit to pull you back in the long run. Developing and maintaining fundamental tools for your organization is a must and part of the job. If you need a tool to be reused in your projects, this is is the way and it will be available for you anywhere an ODI tool is. The biggest difference from KM or procedure, is the fact that open tool will not share the transactions within your package, it is more a global standalone and persistent step in a package. Also, this tool should achieve its goal with minimal functionality, because you would need to be able to have some kind of backwards compatibility when modifying the class you have developed. You can read more on Open Tools in  my previous post.

KMs, Open Tools or procedures? What are you using the most?


Open Tool Tips

Developing an ODI Open Tool is quite simple. Some Java skills and understanding are required, but it is totally not needed to be an expert. The guide is pretty good, it explains how to build OpenTools and how to put them for Oracle Data Integrator to use. The tutorial can be found in the ODI documentation.

The one thing I was missing in the documentation is the jars (probably, I was just too lazy to look further…), which I had to add to my classpath in order  to be able to develop my code in my IDE of choice. The Jar is odi-core.jar, in the 11G version is located at <install directory>/oracledi.sdk/lib/, after adding it to your project classpath you will be able to use Open Tool classes.
Update: The jars which should be included in the classpath are all the jars, that are located at <install directory>/oracledi.sdk/lib/ . Otherwise, the following exception is thrown when trying to use the OpenToolExecutionException in the Execute() method as described in the documentation:

No exception of type OpenToolExecutionException can be thrown; an exception type must be a subclass of Throwable


The process of developing such a tool is straightforward (OK. I am not going copy/paste from the official documentation, so I will just give you the links):

1. Develop the class as described here:
Developing Open Tools

2. Put your jar in the classpath as described here:
Add Additional Drivers and Open Tools

3. Add the tool into ODI following these steps:
Installing and Declaring an Open Tool

So far, so good. Although, as you continue to develop, you will encounter a few questions and maybe even some pitfalls, which are better addressed from the beginning. Some are pointed out int the documentation and some are not.

  1. Do I really need an Open Tool for this task? This is the first question to ask before starting coding. Open Tools are usually things to reuse in multiple packages and multiple projects, which serve a very specific task. If it is not your case, you are better off with a KM or procedure.
  2. The best way to start is by extending the OpenToolAbstract class. If you are extending your own class and since, extending more than more class is not allowed in Java, than make sure you have implemented the IOpenTool interface.
  3. Open tool is not supposed to be a complicated Java class. Overwhelming it with a large amount of parameters and methods is a bad thing.  A much smarter thing to do, is break large difficult tasks, into small and specific multiple mini tasks, then create an Open Tool for each one.
  4. Modifications and updates, if you have deployed many scenarios using your Open Tool and you have created a new version of it, than updating the Jar in the classpath, will result in all the scenarios, packages or anything else using your tool advancing instantly to the new version. This means, that any update should be deployed with much care and backwards compatibility. This is the main reason for me, not to make my Open Tools too complicated.
  5. This is Java. you get, what you have written. Performance, cleanup and bugs are your responsibility.
  6. The parameters must be validated within the Open Tool code. The “Mandotory” Boolean parameter passed when initiating the OpenToolParameter class are just for displaying in the UI and ODI will not validate these parameters are set correctly or set at all, even if the parameter is initiated with this variable set to true.
  7. getSyntax() method is very simple, but has a lot of influence. The name of the tool and the parameter’s initial values will be determined by this method.
  8. Create nice icons for your tools.
Those things are better taken into account before starting writing the code. It is a very simple process and I suggest starting by writing something, that does not require much coding. KISS.


Python Custom Files In ODI

Some time ago I saw an email on one of the email lists in which, the author asked how to import custom python modules into ODI. When reading this email, I did not know, what would be the best way to perform such a thing and that made me a little curious. After a while, trying to find the answer on the net and waiting for replies on the email lists, I have decided to check it out on my own. The best solution, in my opinion, would be using the -Dpython.path Java argument. The main reasons for this would be:

  • Simple.
  • This is more a Jython problem than ODIs and therefore, the solution should be, as well, at Jythons level.
  • Another good thing is that this argument could easily rely on the, well known, $PYTHONPATH environment variable. Resulting, in insurance that all applications are using the same version of the files.
  • Anyone importing custom modules into ODI would like to maintain some kind of portability option, just in case, the decision of moving the python files will come. In this case, all that will be required is changing the $PYTHONPATH environment variable to the new location and restart the agents so the change will take effect.

How To:

1. Configure the $PYTHONPATH environment variable. For example in linux:

export PYTHONPATH=/path/to/my/python/files

2. Edit the ODI_ADDITIONAL_JAVA_OPTIONS in your and append “$PYTHONPATH” to it. For example in linux:


3. Restart.

Now, if you try using your python files they will be successfully imported into Jython from within ODI. Also, to make sure that the changes have taken effect, you can print the sys.path variable from Jython in order to see what is included in your path.

system downtime

Do Not Draw That Red Line

It is a common thing, for a data integration process to have some kind of exception handling, when one of the parts in the flow fail.
The same can be said about ODI, usually when designing a package, in order to handle failing steps people tend to draw a red line from every step in a package to another step, which is supposed to handle the exception and by handle I mean, send an email informing about the failure using OdiSendMail tool. Some even go one step further and after that ODI mail tool they add another step which just throws an exception in order to be able to see those failures in the Operator.

Well, I used to do so and from experience I can tell you, that sending emails informing of failures with the ODI email tool is by far, not the best solution. Maybe it can work for a small flow, which does not consists of many parts, but when working on a big project containing different components and aiming to be able to work with large volumes of data, this will slow you down.


  1. The code – The last thing you want to do is to touch the code already in production just because you woke up one morning and decided to change the email list. Well, you gonna, unless you had taken some kind of actions to avoid it. There are ways to avoid this, by using a variable for the email list or maybe creating an email group, which could be controlled outside of ODI. OK, we are covered… Basically, we are not. What about changing the SMTP server, there are reasons beyond our control, that could lead to such an action. Maybe the subject because of a typo you have not noticed before? Basically, in order to be able to do those things, you will have to maintain as many variable as does the OdiSendMail accepts, total of 8, currently in version. There are at least 2 ways, I can think of right now, how to store the values for those variables, but I will stop here and point out two problems. One, isn’t all this is too much? Just to inform of a failure? Two, no matter how you store the variables, this method still will impact any scenario you have deployed or will deploy, as it is an integrated part of them.
  2. The frustration – When I first started with ODI, I was developing packages like that. To tell the truth, I did not use the send mail tool, but a procedure that would insert the errors into the DB, because we had a little different requirements. Never the less, it was frustrating at the same level, since the concept remained. Each new step, demanded drawing a red line to that exception step. The process was supposed to handle big chunks of data, so basically we went into a loop of developing, testing and changing to meet the requirements, while there were plenty of times that somebody else forgot to draw that red line. To tell the truth, personally, I forgot to do it, every single time :). After doing a few loops like that, the conclusion was obvious.
  3. The looks – When using this method, the more components your flow will have, the less good looking your package will be. Eventually, It will look like a big spider web and make it harder to try and remember what is going on in there. Of course, you can hide the red lines, but after you do that, lets see how many people will remember to show them again or draw it for the new step they have just added into the package. A few weeks ago, I have tried to look into a package we developed like this, that was scary.
  4. Monitoring – From this point of view, I would say, that the scenario is not the part, which should be in charge of failure notifications. The monitoring process should be independent and not integrated into each scenario, more like a “Big Brother”.
  5. Reliability – There are many reasons, why the exception handling step could fail. This means, that the system could be down for a while, before anybody would notice.

Maybe, if I try harder, I would remember one or two more reasons, but lets continue to the alternative. Just with a small select on the repository, it is very easy to get the status of the scenarios that were executed lately. With this method, the monitoring process does not suffer from anything I discussed above. I have written about the repositories in my last article, which can be found here.
Also, I would like to comment, that I am not saying not to use the red line (KO) or the OdiSendMail tool, I think people should use those, but not in this way.

Well, this is becoming a much longer post than I thought. I could go and write about how to do monitoring in ODI, but this sounds like one of my next posts.