Fetch data from different database schema tables

[suresh pilakal babu]

Hello

I want to execute a query, which contains tables from different database schema.
How can i create a single connection object for different schemas.I am using oracle database.
Is it possible?


Thanks,
Suresh.

[Paul Sturrock]

A single statament? So presumably you are just unioning unrelated entities?

There is nothing in JDBC that will let you do this. However, an Oracle only workaround would be to use a global link (see the Oracle documentation) to access one database from another. I think once you have this you can write SQL like:

select * from foo union select * from bar@global_link_name 

and you'll get what you want.

[Paul Campbell]

Originally posted by Paul Sturrock:
A single statament? So presumably you are just unioning unrelated entities?

There is nothing in JDBC that will let you do this. However, an Oracle only workaround would be to use a global link (see the Oracle documentation) to access one database from another. I think once you have this you can write SQL like:

select * from foo union select * from bar@global_link_name 

and you'll get what you want.

You can also do the following in Oracle without the global link:

SELECT * 
 FROM foo@dbname1
UNION
SELECT *
 FROM foo@dbname2

The caveat to this is: your user name and password must be identical for dbname1 and dbname2
[ December 16, 2008: Message edited by: Paul Campbell ]