There is some time that I don’t write a post, it’s because of the amount of work… too much clients, too much process… but all is perfect! I mean, work needs to exists because then it’s necessary people to do it! 😉
OK, today we are going to talk about those two tab’s at procedure step:
Obs.: When I say “Procedure Step” I mean “Procedure or KM Step” once this objects are almost the same but this is subject for another post.
Because of the interfaces, that has source and target tables, a obvious association is done and that is right. The difference is:
- Has Source and Target Table in generic way, KM’s are necessary to define how to push data from source to target normally in several steps
- Source and Target Tab’s at Procedure Step:
- It’s a single action (commands) between a defined source technology and a defined target technology
Before go to examples, a last information:
The most interesting and powerful characteristic of these tab’s is the ability of:
- When a SQL query (select..) is executed in Source tab, the code wrote at Target tab will be executed once to each returned record from the SQL query
After theory and definitions, examples!
- LKM SQL to SQL
- This is the most classical example of functionality and interaction between the tab’s once, after create the C$, it transfers every single record from the query in the source to a “insert” command into target tab (Step 31 – Load Data)
Code at sorce tab - LKM SQL to SQL
Every single record returned from this query into Source Tab (as it is a LKM, the metadata commands will create the right command)
Will be inserted in C$ table by the following command into Target Tab:
Insert command at Target Tab - LKM SQL to SQL
But this a example from a KM, could we try something in a Procedure?
Before tell you how to implement, let me discuss about a very important issue: How are the returned values “delivered” to Target Tab???
Let’s remember few points:
ODI is a code generator
The generated code can be at any programming language or RDBMS command
ODI variables exists only in ODI, they aren’t able to exist inside, for instance, of a PL/SQL code in execution at Oracle database.
A new point that, maybe, you aren’t aware about is:
Note that we will send a query from Postrgre SQL to Oracle it means, two distinct RDBMS.
Agent is able to transform the data format
For Oracle people, this explain how ODI can integrate several Oracle databases with no DBLink.
As I mentioned above, ODI will generate the code and send it to the Oracle database, but how to indicate, in the generated code, where use a column from source?
It’s easy. The query I proposed is:
Select Name, Age from Client
The code for target tab will be:
By using the column name prefixed with “:”, ODI understand that is to generate the Oracle code with the equivalent column from Postre query.
If a function was used in the souce like:
select EXTRACT(DAY FROM TIMESTAMP age) from Client
Just add an Alias and use it at target tab:
select EXTRACT(DAY FROM TIMESTAMP age) as Birthday from Client
Well, that is all friends!
The code at Target tab will be executed some many times as the amount of records returned from the Postgre query.
Just remember to setup the respective technology and Logical schema at each tab.