Query two databases with Oracle SQL Developer

Question

Is there a way to query two databases (in a single query) in Oracle SQL Developer?

I'm not very familiar with Oracle -- aside from standard CRUD syntax at any rate.

I'm trying to do an insert into an Oracle table from a SQL Server table. Would like to do something like this:

INSERT INTO OracleDB.table (field1, 2, ...)
SELECT ... FROM SQLServerDB.schema.table

I've got (working) connections created for both databases in Oracle SQL Developer.

Thanks

--EDIT--

I have no admin privileges to the databases themselves. (Cannot create linked servers, etc.)

So is this read from SQL Server & insert into Oracle?

Sathyajith Bhat
– Sathyajith Bhat

2011-11-17 06:01:17 +00:00
Commented Nov 17, 2011 at 6:01 — Sathyajith Bhat
– Sathyajith Bhat, Commented Nov 17, 2011 at 6:01
Yes (read from SQL-Server & insert into Oracle)

Chains
– Chains

2011-11-18 17:05:42 +00:00
Commented Nov 18, 2011 at 17:05 — Chains
– Chains, Commented Nov 18, 2011 at 17:05

Sathyajith Bhat · Accepted Answer · 2011-11-17 06:12:25Z

Yes, that's possible. Connections in SQL Developer won't help you - you will have to setup a database link from your Oracle database to the SQL Server database.

Before creating a database link, you'll have to setup a Heterogeneous gateway to connect to the SQL Server.

General steps include:

Install Oracle ODBC drivers on the server that will access the remote SQL Server database using the database link.
Setup the ODBC connection on the local Oracle database using the Windows ODBC Data Source Administrator
Test the ODBC drivers to ensure that connectivity is made to the SQL Server database.
Configure the Oracle Heterogeneous services by creating an initodbc.ora file within the Oracle database.

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_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME=ora_sid) -- Enter the DSN on this line
    (ORACLE_HOME = c:\oracle10gdb) -- Enter your Oracle home on this line
    (PROGRAM = hsodbc) ) )

Modify the Tnsnames.ora file to point to the gateway

    (DESCRIPTION=
    (ADDRESS_LIST=
    (Address=(PROTOCOL=TCP)
    (HOST=
    -- (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
    )

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

Once the database link is created, you should be able to insert into the database using a simple:

 insert into <oracle_tablename>
 select * from <sqlserver_table_name>@dblink_name

Further reading:

Oracle documentation on Heterogeneous Gateway connectivity
Oracle documentation on creating Database links
Making a connection from Oracle to SQL Server ( this is slightly old, but very good, illustrated article!)

Matt · Accepted Answer · 2011-11-17 06:12:42Z

2

You can read data from an ODBC connection using Heterogeneous Services. It is a bit of a pain to set up though. You have to edit several files in the oracle install to setup a new TNS Listener then create a dlink within your schema/global.

This guide is for unix but is the shortest/least retarded one IMO.

The other option is to pipe data via a 3rd party program. For instance I think you can create a query in MS Access then export the data to Oracle or SQL Server.

answered Nov 17, 2011 at 6:12

Matt

1,2421 gold badge9 silver badges18 bronze badges

Collectives™ on Stack Overflow

Query two databases with Oracle SQL Developer

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related