Most developers that start working with Oracle Data Integrator (ODI) and create their first mappings will stumble upon an error message like this pretty early in their ODI career:
In this blog post, I will first recreate this error with ODI 12.2.1.2.6 and then show what you can do about it. For understanding this post, you should have some basic ODI knowledge (what mappings are, that they have a logical and a physical design, and that you can use different knowledge modules in the latter).
The Problem
In my example, I get files with revenue data from cinemas and load them into an Oracle database table. To make sure that I don“™t get in any trouble when a cinema shows the same film more at the same time more than once (maybe everybody wants to see the new Star Wars film), I add an aggregator between source datastore (the file) and target datastore (the database table):
For the target datastore, I chose the knowledge module IKM Oracle Incremental Update (MERGE) that uses an intermediate table (what ODI calls I$ table) and allows for Flow Control (validating the loaded data). Apart from that, I didn“™t do any other changes in datastore properties or physical design.
When I run this mapping as it is, I can see in the Operator tab of ODI Studio that it fails:
When I open the task where the error happened, I see the aforementioned error message:
When I look at the target code, I find the reason for this error:
The mapping selects the next sequence value for the primary key column of my target datastore in the same SELECT statement where I group by the source attributes. Unfortunately for me, Oracle database doesn“™t accept that.
How can I deal with this problem? There is a relatively obvious and a not-so-obvious way. First, let“™s talk about the relatively obvious one.
The Relatively Obvious Solution
I open the mapping again and click on the primary key attribute of the target datastore (the one where you select the next sequence value). As Execute on Hint, I select „Target“.
When I run the mapping again, I don“™t get the error anymore but I still get a warning:
When I look at the session details, I see the reason for this warning: The mapping completed its run but two erroneous records were found during flow control (in task 260 when looking for potential not null constraint violations, to be precise):
The mapping has put these records into an error table (ODI calls them E$ table) that I can query using ODI Studio or the database development tool of my choice:
That way, I can understand what happened: I didn“™t insert any values for the primary key column into my I$ table but I still checked if this column was null (which it was since I didn“™t insert any values). This means I have to go back to the datastore properties.
For the primary key attribute of the target datastore, I have to un-check the box that says Check Not Null (Flow Control only):
When I run the mapping again after this change, it finishes without any errors or warnings.
That was the first, relatively obvious way of dealing with our ODI sequence error. The second, not-so-obvious way is changing the knowledge module I use in the mapping.
The Not-So-Obvious Solution
In the Global Objects section of ODI Studio“™s Designer tab, I duplicate the knowledge module IKM Oracle Incremental Update (MERGE) and give it a new name like IKM Oracle Incremental Update (Sequence).
In the new knowledge module, I have to change two tasks: the one called Insert flow into I$ table where data is written into the intermediate table and the one called Merge Rows where I update the target datastore.
In the Insert flow into I$ table task, I have to make sure that I don“™t insert any values for the primary key column (the one that is populated by the sequence). I achieve this by adding „and !PK“ both to the column lists of the I$ table itself and the SELECT statement that is used to fill it:
The meaning of selectors like „TRG“ and „PK“ can be found in the ODI Substitution API Reference that is a valuable resource when working with knowledge modules.
In the Merge Rows task of my new knowledge module, I make a similar change so that the mapping won“™t load the values for the primary key column from the intermediate table anymore:
Sometimes it“™s good to remember your Boolean logic: the negation of „and !PK“ is, of course, „or PK“.
What have I achieved with these knowledge module modifications? Thanks to them, I won“™t have to worry about the correct Execute on Hint settings anymore:
I just have to make sure that in the physical design of my mapping, the new knowledge module is set for the target datastore:
If I always fill my primary key columns with a sequence, I can even set this knowledge module as default so that all future mappings will use it:
When I run the mapping with the new knowlege module, it finishes without any errors or warnings:
When I look into the generated code for the Insert flow into I$ table task, I see the difference the knowledge module changes made:
As expected, the I$ table ignores the primary key column and everything works fine. That was the second, not-so-obvious way of dealing with our ODI sequence error.
Lessons Learned
Is there a lesson to learn from this? I think there are at least two:
- Almost always, there are multiple ways to solve an ODI problem.
- If the way you found doesn“™t include fiddling around with knowledge modules, maybe it should.
