Skip to Main Content
  • Questions
  • Transfer table from sql server to oracle db

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Subrat.

Asked: January 09, 2018 - 10:47 am UTC

Last updated: January 31, 2018 - 12:04 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Team,
Thanks for your continues help.

I want transfer one table(iccpayment) from sql server to oracle db. And also i want create schedule to this table because everyday user load data to sql server table same also happen in oracle.

Please help me on this.




Thanks,
SKP

and Connor said...

The most robust method would be to use Oracle Goldengate but that might be overkill for your requirement. (It is also a licenseable product)

http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html

The other option is hand-coding the transfer. First you need to setup heterogeneous services

https://asktom.oracle.com/pls/apex/asktom.search?tag=connectivity-to-sql-server

but in a nutshell
- setup a database link called (say) "dblink"
- this points to a tns entry called "sqlserv"
- in $ORACLE_HOME/hs/admin you initSQLSERV.ora
- this points to an ODBC entry which connects to your SQL Server database

And then you'll be using SQL to copy the data over. SQL Server has case-sensitive names, so typically queries will look like:

insert into my_oracle_table
  (COL1,
   COL2,
   ...
  )
  select 
     rtrim("Col1"),
     rtrim("Col2"),
     ...
  from MY_SQL_SERVER_TABLE@dblink;
  


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Heterogeneous information

Michael Seberg, January 30, 2018 - 2:47 pm UTC

Great answer, but the link for it points to another link which points to an Oracle 8 document. I would consider referring to E17907-06 instead.

Connor McDonald
January 31, 2018 - 12:04 am UTC

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database