?
Solved

How to copy data from sql server to oracle

Posted on 2013-05-14
6
Medium Priority
?
587 Views
Last Modified: 2016-02-10
Hi

The problem is we need to copy data routinely ie set up automation to copy data from a sql server database to an oracle.

What would you recommend?

I looked into doing it with an SSIS package but we have server 2012 and needs to interact with the oracle client to work. Is there a way of firing the data strait into the oracle database without client software etc necessary on oracle server? Thanks
0
Comment
Question by:jazz__man
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39165232
Except for the JDBC Thin client connection (and some 3rd party connection apps), to connect to an Oracle database, you need the Oracle client installed.

You might look at pulling the data instead of pushing the data.

You can set up Heterogeneous Services on the Oracle side to pull the data from SQL Server.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/ds_concepts001.htm#ADMIN12078
0
 
LVL 1

Expert Comment

by:enaliev
ID: 39165262
Hi,

as an alternative, you can use some ETL solution for this task.
E.g., you can use Talend Open Studio to create a package (application) and run it on demand.
0
 
LVL 16

Accepted Solution

by:
Walter Ritzel earned 2000 total points
ID: 39167726
If you want to transfer data between the 2 databases, but dont want to change servers configuration by installing any new type of software, I believe your best alternative would be an ETL Tool, like the one suggested above. The other point is: do you know if your SQL Server supports Oracle linked servers (http://support.microsoft.com/kb/280106?wa=wsignin1.0)
If yes, a simple creation of a linked server on sql server side would allow you to create a t-sql procedure to insert whatever information you want on oracle.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39167772
wpcortes,

Step 1 in the link you posted:  
You must install the Oracle client software on the computer that is running SQL Server where the linked server is set up.
0
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 39168005
Thanks, Slightwv, that was exactly what I was trying to get the author to answer. Because his approach was using SSIS, but was unconfortable with the idea of adding new software. But if the ability to create linked servers to oracle, then this should be no concern.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39168203
OH, They may already have the Oracle client installed.  I get it know.  Sorry.
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question