Hi All,

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:

• Source Tab
• Target Tab

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:

• Interface:
  • 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

Insert command at Target Tab - LKM SQL to SQL

• Send records fom a Postgre SQL table to an Oracle procedure
• Source Query:
  • Select Name, Age from Client
• Target Oracle Procedure:
  • pClient(Name as varchar, Age as date)

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:

• The responsible to execute the interaction between the Source and Table tab is the Agent

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:

Begin

   pClient(:Name, :Age);

End;

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.

 Best Regards,

Cezar Santos