Home » RDBMS Server » Server Utilities » data from M$ SQL into Oracle DB (Oracle 10g)
data from M$ SQL into Oracle DB [message #601848] Tue, 26 November 2013 06:47 Go to next message
rc3d
Messages: 133
Registered: September 2013
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 Go to previous messageGo to next message
ThomasG
Messages: 3064
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

Re: data from M$ SQL into Oracle DB [message #601853 is a reply to message #601850] Tue, 26 November 2013 08:10 Go to previous message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
>Another Option could be to connect to BOTH Oracle and SQLServer from the Java file
same can be done using PERL
Previous Topic: Error during Impdp "ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <table_name>
Next Topic: Services restart during datapump expdp and impdp
Goto Forum:
  


Current Time: Fri Apr 18 04:07:40 CDT 2014

Total time taken to generate the page: 0.04902 seconds