Data from Oracle Server to SQl server

I used  SQl query to select data from SQl serverA  via DTS package to a table in SQL ServerB.  
SQL serverA is now an Oracle server.  How can I use the same query to still select the same data to SQL serverB from  ORACLE
Nana HemaaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


As I recall, the  DTS package can connect to an Oracle database (perhaps through ODBC?)
Once connected you can issue the same query.

PS: Maybe you can get the details of "how to" in the SQL server forum.
Nana HemaaAuthor Commented:
I cannot use the DTS package any longer.  Any other ways to select data from oracle Db to sql  database table

You could install what is called in the Oracle world 'Heterogeneous Connectivity" (or SQL server gateway) and then both databases can talk to each other.

Another way (depending pn your requirements) would be to install Oracle client on SQL server machine and code an SQL*Plus script to extract the data into a local text file and load from there.

But the easiest would be to use an SQL Server utility like SSIS.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
you can use this too
    1. Install Oracle ODBC drivers on the server (your local Oracle database) that will access the remote SQL Server database using the database link.

    2. Setup the ODBC connection on the local Oracle database using the Windows ODBC Data Source Administrator

    3. Test the ODBC drivers to ensure that connectivity is made to the SQL Server database.

    4. Ensure that your global_names parameter is set to False.

    5. Configure the Oracle Heterogeneous services by creating an initodbc.ora file within the Oracle database.

    7. Modify the Listener.ora file.

        SID_NAME is the DSN for the remote database.
        ORACLE_HOME is the actual Oracle home file path.
        PROGRAM tells Oracle to use heterogeneous services.

        (SID_LIST =
        (SID_DESC =
        (SID_NAME=Cas30C) -- Enter the DSN on this line
        (ORACLE_HOME = c:\oracle10gdb) -- Enter your Oracle home on this line
        (PROGRAM = hsodbc) ) )

    8. Modify the Tnsnames.ora file. This is the Oracle database installation accessed by the users to connect to the ODBC drivers

        -- (Server x)
        (PORT=1521))) -- Enter the port on which the server x Oracle installation
        -- is listening
        (CONNECT_DATA=(SID=Cas30c)) - Enter the DSN name
        (HS=OK) -- Enter this value. It tells Oracle to use hetergeneous services

    9. Reload the listener on local Oracle database

    10. Create a database link on the local Oracle installation that accesses the heterogeneous connection, which, in turn, connect to SQL Server.

    11. Run a SQL Server Select statement from the Oracle installation using the database link.

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.