topic Re: how to load incremental data from oracle database to MSSql database in Talend Studio

how to load incremental data from oracle database to MSSql database

Anonymous — Sat, 16 Nov 2024 11:55:39 GMT

I want to load incremental data from oracle database to MSSql database where data has to insert into MSSql database table with date as filter criteria.
Suppose Oracle Db table contains data till 16 jul 2013 and MSSql table also contains data till same date.Next Day,In oracle db table new records will come of 17 jul 2013,then Newly loaded data will get inserted into MSSql db table(Incremental Basis).
i.e. Oracle_Databse.table.Date > (Select max(table.Date) from MSSql_Databse.table)
I am using tOracleinput as Source where Query would be like
Select * from Oracle.Table_Name
Where Date > context.Date
context.Date="Select max(Date)as Max_Date from MSSql.table"
It gives me Error ORA-00904 while execution.
Kindly suggest what to do??

Re: how to load incremental data from oracle database to MSSql database

Anonymous — Tue, 27 Aug 2013 17:38:35 GMT

I think your query is more likely to be...
"Select * from Oracle.Table_Name
Where Date > " + context.Date
You may need to wrap your context variable in to_date() depending on its format.
I'd strongly recommend reading about change data capture, for example, on Wikipedia first.
I'd also suggest naming you columns rather than relying on "*" to retrieve your columns in the expected order.

Re: how to load incremental data from oracle database to MSSql database

alevy — Wed, 28 Aug 2013 00:47:47 GMT

You can't assign SQL or Java expressions to a context variable, only their result. Anyway, I don't see how you expect Oracle to execute a query on SQL Server in the first place!
You need to put "Select max(Date) from MSSql.table" in a tMsSqlInput and pass the result to tJavaRow where you can assign it to your context variable.
You also, as tal00000 says, need to separate the context variable from the rest of your Oracle SQL string so its value can be evaluated and appended. But you need to format the date appropriately as well e.g.
"Select * from Oracle.Table_Name
Where Date > '" + TalendDate.formatDate("dd/MM/yyyy",context.Date) + "'"

Re: how to load incremental data from oracle database to MSSql database

Anonymous — Thu, 29 Aug 2013 06:45:43 GMT

Hey tal00000 and alevy Thanks for the reply...
My Job Flow is as follows
toracleinput---->tmap----->tmssqloutput
and my oracle query is same as tal00000 interpreted
Select * from Oracle.Table_Name
Where Date > " + context.Date
but context.Date contains a MSSQL Query(Select max(table.Date) from MSSql_Databse.table)
but i got the error like "Missing Expression" OR "Context is invalid identifier" when i run the job.
Kindly suggest me

Re: how to load incremental data from oracle database to MSSql database

Anonymous — Thu, 29 Aug 2013 07:36:42 GMT

hi,

Context is invalid identifier

are you using Context OR context ?

context.Date contains a MSSQL Query(Select max(table.Date) from MSSql_Databse.table

Don't understand well ! Are you request DB and store result in context variable 'Date' ?
PS: avoid some 'key word' like Date to name your variable.
regards
laurent

Re: how to load incremental data from oracle database to MSSql database

alevy — Thu, 29 Aug 2013 07:52:55 GMT

Read what I wrote. You CANNOT put a SQL statement into a context variable and expect it to be run!