CODEWITHSUNDEEP
.netvb.netoracle-databasesubquery
n00b
n00b

Reputation: 4737

Nested queries from multiple Oracle databases

In .NET, Is it possible to run a nested query from two separate Oracle databases?

E.g.

"SELECT my_value, FROM table_in_database_1 WHERE my_value IN ( SELECT my_value FROM table_in_database_2 )"

If so, how would I go about doing this?

Ultimately, this is an effort to overcome an "ORA-01795" error from using an "in" statement with over 1000 items in the conditional list, without having to break the query out into multiple "OR value IN" lists.

Upvotes: 2

Views: 830

Answers (3)

DCookie
DCookie

Reputation: 43523

You might be able to get away with simply building a database link and joining over that link, but that can have performance issues. Something like this:

Build a link to database 2 on database 1.

CREATE DATABASE LINK db2 CONNECT TO user IDENTIFIED BY pw USING tns-alias;

Join your table on database 1 to the table on database 2:

SELECT my_value 
  FROM table_in_database_1 t1 JOIN table_in_database_2@db2 t2
       ON t1.my_value = t2.my_value

Depending on the performance of the link, you may opt to use a hybrid approach for this, involving both a database link and a temporary table.

Build a database link as above. Build a temporary table on database 1 that holds the values to be used in the subquery from database 2.

CREATE GLOBAL TEMPORARY TABLE db2_values (value VARCHAR2(20));

Copy the values from db2 to db1:

INSERT INTO db2_values
SELECT my_value 
  FROM table_in_database_2@db2;

Finally, join your database 1 table to the temporary table. 

SELECT my_value
  FROM table_in_database_1 t1 JOIN db2_values t2 ON t1.my_value = t2.value;

Upvotes: 1

Florin Ghita
Florin Ghita

Reputation: 17643

If you can't make a database link, you can also insert into a temp table in database_1 all values from SELECT my_value FROM table_in_database_2 and after that make the join.

Upvotes: 1

diagonalbatman
diagonalbatman

Reputation: 17992

Yes - you can look into Database Links for this.

Upvotes: 0

Related Questions