ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Procedures (or KM) Steps – Source and Target tab’s

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

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

Insert command at Target Tab - LKM SQL to SQL

 
But this a example from a KM, could we try something in a Procedure?
 
Situation:
  • 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

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

 

22 Comments

  1. I am using ODI11g and have written a procedure, with a command on source and command on Target.

    In Command on source, i have used the technology Oracle and this my piece of code:

    Select ‘SCN1’ SCN from dual
    union
    Select ‘SCN2’ SCN from dual

    in Command on Target, i have used the technology “ODI tools” and this is the code:

    OdiStartScn -SCEN_NAME=#SCN -SCEN_VERSION=-1

    OdiStartScn -SCEN_NAME=DUMMY -SCEN_VERSION=-1

    upon executing i get a lexical error. what is the format of checking the values coming as output of Command on Source.? how to i write the above the code…I am more interested in how to check the # values?

  2. Hi Team,

    I had came across one ODI Issue.

    My source and target table,having differnt columns name
    i.e for example lets say my
    Suppose my source table name is Source_Test and respective column name are a,b,c,d,e
    and
    my target table name is target_test and respective column name are f,g,h,i,j

    so
    mapping would be

    a with f
    b with g
    c with h
    d with i
    e with j

    In my real scenario like wise there are 250 columns in source and target table

    so i am doing manual mapping from column to column

    so could u please provide some solution on the same.

    Thanks & Regards,
    Prashant Reddy

    • Hi,

      unfortunately in this case you need to perform the mapping one by one. The automatic mapping from ODI needs to have the same name of columns to match the columns.

  3. Hi Kalyan,

    Good to see you around…

    Please, send me an email in cezar.santos@odiexperts.com with a sample of the ODI 11g generated file…

  4. Hi All,

    We have migrated from sunopsis to ODI recently .The interface is quite simple generating files with double quotes to delimit the fields. the fields are separated by ;
    the result is different with ODI than with sunopsis. we only migrate and don’t change the interfaces.

    with sunopsis a field contains a ” character : sunopsis double it automatically
    now ODI does nothing on the quotes and we get unbalanced quotes.

    I have used the following Datastore- Files Delimiter options.
    Datastore -> Files Tab
    a) File Format – Delimited
    b) Heading (number of lines) – 0
    c) Record Separator – MSDOS
    d) Field Separator – Other (;) Hexadecimal (3B)
    e) Text Delimiter – ”
    f) Decimal Separator – ,

    Request for your valuable pointers on this.

    Thanks & Regards,
    Kalyan

  5. Hi Kshitiz!

    When I used the function odiRef.getColList, the columns names in the query select generated were created with quotes, for example:
    select
    PRODUCTS.”cd_product”
    PRODUCTS.”desc_product”

    When that query runs, an error happens. In my case, Oracle doesn’t know columns with quotes. I think that’s a database configuration, I don’t know…

    So, I’d like to know if is possible to edit the LKM SQL to SQL to use some function (or something like this) to remove the quotes. Or if I can to solve the problem through another way…

    Thanks!

    • The reason is that ODI automatically applies the column name with double quotes if the column name is in lowercase .
      The solution is to use the capital name for the column name and try , ODI won’t add the double quote then.
      So cd_product should CD_PRODUCT ,desc_product should be DESC_PRODUCT

      • Hi,
        I know that post is pretty old but I am facing the problem right now.
        My source table has lowercase column names and while mapping ODI adds double quotes and switches mapping on target.
        How can I turn adding double quotes off?
        Thank you.
        ODI 11.1.1.7

        • Hi…

          Go to topology, edit de technology itself and in the Advanced Tab take off the quotes as delimiter…

          Does it works?

          • Thank you for the reply.
            If you meant Column-Alias Seperator as delimeter, it was already empty.
            I also tried to put double quotes save and delete it again but no luck.

          • BTW; my source is progress, if it helps.
            Thank you.

          • What are the source and target Technologies?

          • My source is Progress and target is Oracle.

          • Make no sense…once you take off the quotes from Technology it shoud work.

            What happens if you cut off the quotes in the mapping?

          • My oracle target table and columns is all uppercase,
            My progress source table and columns is all lowercase,
            When I add source and target to my interface, ODI does not map any column auto,
            So I do it by dragging,
            When I drag the source column to target column, ODI adds double quotes to column name (ex: products.”product_id”),
            And changes “Execute on” to “Target” which should stay as source,
            Then I delete double quotes and change execution to source and it works fine.
            What I want to do is is when I do a dragging I dont want ODI to add double quotes.
            Dont get me wrong, I just try to be clear to prevent any useless effort.
            Thank you.

          • Of course… don’t worry about question it. It should work, so simple like that!

            Just for be sure… check again if Postgree technology has no double quotes at Topology. It’s the place to change the behavior…I never saw that do not work!

          • well this is my topology 🙂
            http://tinypic.com/r/3acuo/8

          • HEY!!!!
            it worked, I was checking progress side but i took off qoutes from delimeter at oracle technology and its ok now.
            thank you for your help.
            cheers.

  6. Hey guys!

    In my case, the function snpRef.getColList returns quotes in the columns names.
    How can I remove them?

    Thanks!

    • Hi Josue,
      Thanks for visiting us. Can you please try this way .

      In case you are still getting error , can you please let me know what is the code you are using , so we can guide you accordingly.

      Thanks
      Kshitiz Devendra

Leave a Reply

Required fields are marked *.