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)Â
-
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.
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
Â
October 4, 2017 at 12:39 PM
Hello Experts,
Please help us to resolve below issue.
We are facing different issue while we try to delete data using procedure.
Steps we followed :
-Create a procedure where in source command we kept select query. “SELECT DOC_ID FROM SOURCE_TAB”
-In target command we kept delete query which suppose to delete only unmatched data from “TARGET_TAB”.
“DELETE FROM TARGET_TAB WHERE TRAN_ID NOT IN (:DOC_ID)”
-But our concern is here, procedure executes successfully and its delete all data from target table where source and target table data has same.
Source table: SOURCE_TAB
DOC_ID NUMBER(10);
====
10
20
30
40
50
Target table : TARGET_TAB
TRAN_ID NUMBER(10);
====
10
20
30
40
50
NOTE: Source and target table data is sale. It should not delete any data.
October 4, 2017 at 1:07 PM
Hi,
as this “Source/Target” commands executes the source command once per returned line from target the way you developed using “not in” in the moment that the first command is executed every line but ID 10 is deleted, when second line is executed, every line but 20 is delete. So at second line from target all lines are deleted.
October 5, 2017 at 4:21 AM
Hey Cezar,
Thanks for your quick responds, can U plz suggest how can I achieve my requirement is this case..
October 5, 2017 at 12:39 PM
In this case, a possible solution could be:
1. create a temp table on target DB to receive the “DOC_ID” (just one column is enough),
2. change the “delete not in” DML to “insert” into the created temp table
3. add a new step in the procedure
4. delete the target where not exists in the temp table
Just remember to add a previous step to always delete the temp table before populate it for each execution, that step can be at same procedure…
Make sense?
October 11, 2017 at 11:18 AM
Thanks Cezar Santos,
We have achieved above problem with changing solution in different way.
September 16, 2014 at 7:18 AM
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?
September 16, 2014 at 7:20 AM
the post has removed few java code lines…so typing it again…
if(#SCN==”SCN1″)
{
OdiStartScn -SCEN_NAME=#SCN -SCEN_VERSION=-1
}
else
{
OdiStartScn -SCEN_NAME=DUMMY -SCEN_VERSION=-1
}
September 16, 2014 at 11:53 AM
Hi,
Could you send me my email the code and the error raised?
June 5, 2014 at 7:29 AM
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
June 5, 2014 at 12:51 PM
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.
June 7, 2011 at 11:57 AM
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…
June 6, 2011 at 8:27 PM
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
October 22, 2010 at 11:38 AM
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!
October 22, 2010 at 2:23 PM
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
April 7, 2014 at 8:31 AM
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
April 7, 2014 at 1:48 PM
Hi…
Go to topology, edit de technology itself and in the Advanced Tab take off the quotes as delimiter…
Does it works?
April 7, 2014 at 2:16 PM
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.
April 7, 2014 at 2:17 PM
BTW; my source is progress, if it helps.
Thank you.
April 7, 2014 at 2:21 PM
What are the source and target Technologies?
April 7, 2014 at 2:41 PM
My source is Progress and target is Oracle.
April 7, 2014 at 2:53 PM
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?
April 7, 2014 at 3:19 PM
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.
April 7, 2014 at 5:51 PM
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!
April 8, 2014 at 7:36 AM
well this is my topology 🙂
http://tinypic.com/r/3acuo/8
April 8, 2014 at 8:55 AM
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.
October 22, 2010 at 6:06 AM
Hey guys!
In my case, the function snpRef.getColList returns quotes in the columns names.
How can I remove them?
Thanks!
October 22, 2010 at 9:29 AM
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