query from different oracle connections in sqldeveloper

Question

I have 2 connections with different tables in sqldeveloper.

let's say:

ConnectionA with tables: A,B,C ConnectionB with tables: D,E,F

Now I want to have a query that looks like this:

select aa.name,dd.id from A aa,D dd;

How can i do this?

Answer 1

If you want to query objects in two different databases using a single SQL statement, you would need to create a database link between the two databases. A database link is an object that resides in the database and is independent of the query tool. In database A, for example, you could create the database link

CREATE DATABASE LINK to_b
  CONNECT TO username IDENTIFIED BY password
  USING tns_alias_on_a_pointing_to_b

And then when you connect to A, you could do something like

SELECT aa.name, dd.id
  FROM a aa,
       d@to_b dd
 WHERE aa.some_key = dd.some_key

Answer 2

Apparently TOAD Data Point supports Cross-Connection Queries , see:

http://dev.toadfordataanalyst.com/webhelp/Content/Query_Builder/Create_CrossConnection_Queries.htm

Also Oracle SQL Developer seems to support something similar. (see this blog post: Cross Connection Queries)