INFORMIX vs ORACLE
Hi.
Can be ORACLE make a select from some tables on diferent database?.
In my INFORMIX JDBC I can make a query to relation two Instances, using
DATABASE@INSTANCE:TABLE
something like that:
try{
Class.forName(drv);
connection = DriverManager.getConnection(url, user, pwd);
stmt = connection.createStatement();
ResultSet resultSet = stmt.executeQuery("SELECT a.num_emp, b.salary
FROM catalog@ifx_db01:c_employments a,
contable@ifx_db02:payments b
WHERE a.num_emp=b.num_emp");
ResultSetMetaData metaData = resultSet.getMetaData();
......
How Can I do this with some JDBC from ORACLE between SID's?
Its posible?
I have Oracle Entreprise Edition Release 3 version 8.1.7.
I need something additional?...
Thanks in advance
As I understand the JDBC drivers are xopen compliant. That being said it should be possible to run transactions neatly across both databases, but not as I understand it link two tables directly.
I could suggest you create a memory database where you build both result sets up and then do the join select in memory (how big is your resultset). You could do this manually or you could use the hsql driver at http://sourceforge.net/projects/hsqldb/ it is free and works well.
Alternativly keep a duplicate of the smaller table on one of the db's
Best of luck
Travis
Hi Travis, thank you for the reply..
Both tables are too big, one with 22190 rows and other with 10980 and both is growing.
The code sample was not use the real tablenames, but show the problem.
I will try to use the hsqldb, but I don't think that I can be include in my project.
Do you know how can I select a join from two tables in diferent ORACLE SID?.
I think Informix is better to manage link between instances...
You should be able to create an Oracle database link between the two schemas and query the tables the same way as you do with Informix.
Basically....
If the schema information is the same on both databases you can...
CREATE database link my_link
USING 'SID' -- as stored in you database TNSNAMES.ORA file.
If the schema information is different...
Create database link my_link
connect to user identified by password
using 'SID' -- as stored on you database TNSNAMES.ORA file.
You can then query the table as follows:
select * from my_table@my_link
HTH
Simon
