| data from M$ SQL into Oracle DB [message #601848] |
Tue, 26 November 2013 06:47  |
 |
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
|
Senior Member |
|
|
Hi
I have several tables (in SQL Server) which I need to import in our Oracle db. I do this
- query SQL Server with JDBC/Java and write file to *.csv
- use SQL*Loader to import *.csv into Oracle table
- automate step 1 and 2 with shell script / crontab.
Box is Solaris 10
Now my question to experienced DB pros: is there a better solution (without modifying SQL Server)?
For example Oracle DB links are awesome; you can query live data.
|
|
|
|
| Re: data from M$ SQL into Oracle DB [message #601850 is a reply to message #601848] |
Tue, 26 November 2013 07:26  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
I did set up Heterogenous Services links via ODBC from Linux and AIX Oracle servers to SQL Server using unixODBC.
The "how to create a database link that accesses a ODBC data source" is described here.
When you are on windows there are of course SQL Server ODBC drivers, if you are on *nix you can use the FreeTDS ODBC driver.
It is 'a little buggy' when dealing with specific data types and/or mixed case table names when you run oracle queries directly, when you run into that problems you can probably switch to running the query using DBMS_HS_PASSTHROUGH (more compact example)
Another Option could be to connect to BOTH Oracle and SQLServer from the Java file, and do the processing in there. (I did that for a "comparison" program once, where pulling hashes of values from both Oracle and SQL Server and then comparing them in memory was WAY faster than doing anything with DB Links.)
[Updated on: Tue, 26 November 2013 07:26] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
