|
|
|
Forum Member
 
Group: Forum Members
Last Login: 1/25/2007 11:21:00 AM
Posts: 25,
Visits: 1
|
|
Hi,
I need to put data in a Oracle table picking data from a Teradata table and vice-a-versa i.e Load Oracle table using data from Teradata table.Is that possible.Is there some kind of a DBLINK between oracle and teradata.If yes then Can someone help me out with the settings that i have to be put in to have a transparent gateway between oracle and teradata
Thanks in advance
Regards
Rennie
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/3/2006 2:56:00 PM
Posts: 13,
Visits: 1
|
|
You don't need the transparent gateway. You can do the same thing with heterogeneous services. Install the teradata ODBC driver on your database server, and then you configure a listener to point to that odbc driver. Then you can point a DB Link to the new listener.
The real issue is that oracle won't run the sql that teradata requires very easily, and it refuses to pass an outer join to teradata.
You can send native sql to teradata using the DBMS_HS_PASSTHROUGH virtual package. The setback of this option is that the lex compiler in oracle will overflow the buffer if your sql is longer than around 1500-2000 characters.
It's not the most fun thing to configure, but with google you can get it up and running in a couple of hours.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/26/2006 5:46:00 AM
Posts: 1,
Visits: 1
|
|
You could use an ETL tool to manage the connections for you. There are some good ones out there these days. Even some CRM campaign management tools can perform some basic ETL type activity for you. I have also used fast export (and to a smaller extent bteq ) to generate a datafile which you can then feed into Oracle sqlloader utility via a pipe (on unix) directly into the table in question. The same can method can be used in reverse only use Oracle sqlplus to generate the datafile to be loaded and use multiload/ fastload to load the data into Teradata once again making use of the pipe.
I generally extract from Oracle and load into Teradata and use the pipe method without any problems. It's particularly good if you don't have any ETL tools at your disposal.
MG
|
|
|
|