Joining tables from two separate oracle databases possible

Joining tables from two separate oracle databases possible

Post by Will Kram » Fri, 02 Mar 2001 05:16:14



I'm have oracle 8.1 client installed on my NT box and two databases I
have access to via tcp/ip and mention in tnsnames.ora.  Is there a way
via sql to access both databases at the same time and do a simple
join...like  lastname to lastname???  

If not are there ways of  doing this? do I need third party software?

any help , or directions to the right spot would be greatly
appreciated.

regards,
Will Kramer

 
 
 

Joining tables from two separate oracle databases possible

Post by Darry » Fri, 02 Mar 2001 17:17:57


Will,

One way you can definitely do it is through MS Access - I've done it many
times.  You'll need separate ODBC datasources on the computer.  In the
Access database, create links to the tables, then build the join query as
you normally would.

Darryl


Quote:> I'm have oracle 8.1 client installed on my NT box and two databases I
> have access to via tcp/ip and mention in tnsnames.ora.  Is there a way
> via sql to access both databases at the same time and do a simple
> join...like  lastname to lastname???

> If not are there ways of  doing this? do I need third party software?

> any help , or directions to the right spot would be greatly
> appreciated.

> regards,
> Will Kramer


 
 
 

Joining tables from two separate oracle databases possible

Post by Ian Ledzio » Fri, 02 Mar 2001 17:56:10


Have you tried a Database link?

CREATE DATABASE LINK my_link USING '<remote_connect_string>';

You can refer to tables in the remote DB using:



Quote:> I'm have oracle 8.1 client installed on my NT box and two databases I
> have access to via tcp/ip and mention in tnsnames.ora.  Is there a way
> via sql to access both databases at the same time and do a simple
> join...like  lastname to lastname???

> If not are there ways of  doing this? do I need third party software?

> any help , or directions to the right spot would be greatly
> appreciated.

> regards,
> Will Kramer

 
 
 

Joining tables from two separate oracle databases possible

Post by Will Kram » Sat, 03 Mar 2001 05:57:46


can you give an example of a remote conect string??

thanks,
Will

On Thu, 1 Mar 2001 09:56:10 +0100, "Ian Ledzion"


>Have you tried a Database link?

>CREATE DATABASE LINK my_link USING '<remote_connect_string>';

>You can refer to tables in the remote DB using:




>> I'm have oracle 8.1 client installed on my NT box and two databases I
>> have access to via tcp/ip and mention in tnsnames.ora.  Is there a way
>> via sql to access both databases at the same time and do a simple
>> join...like  lastname to lastname???

>> If not are there ways of  doing this? do I need third party software?

>> any help , or directions to the right spot would be greatly
>> appreciated.

>> regards,
>> Will Kramer

 
 
 

Joining tables from two separate oracle databases possible

Post by Ian Ledzio » Sat, 03 Mar 2001 16:40:18


The Cnnect Sring is user defined when you set up your TNSNAMES.ORA file (if
you take that route).  In the case of a DB link, connect string used is the
one from TNSNAMES.ORA file on your server, so you have to ensure that
there's consistency across your system.

The naming convention we use is Server_Application_Environment, so an
example would be: DBS1_ACCT_TEST, DBS1_ACCT_PROD etc..


> can you give an example of a remote conect string??

> thanks,
> Will

> On Thu, 1 Mar 2001 09:56:10 +0100, "Ian Ledzion"

> >Have you tried a Database link?

> >CREATE DATABASE LINK my_link USING '<remote_connect_string>';

> >You can refer to tables in the remote DB using:




> >> I'm have oracle 8.1 client installed on my NT box and two databases I
> >> have access to via tcp/ip and mention in tnsnames.ora.  Is there a way
> >> via sql to access both databases at the same time and do a simple
> >> join...like  lastname to lastname???

> >> If not are there ways of  doing this? do I need third party software?

> >> any help , or directions to the right spot would be greatly
> >> appreciated.

> >> regards,
> >> Will Kramer

 
 
 

Joining tables from two separate oracle databases possible

Post by Will Krame » Sun, 04 Mar 2001 11:38:37


Ok tried what you said...and here's what I got. I'm still missing
something.


SQL> /

Database link created.

SQL> select a.firstname, a.lastname, b.fname, b.lname

  3    where upper(a.lastname) like 'AR%'
  4      or upper(b.lname) like 'AR%'
  5  
SQL> /

                           *
ERROR at line 2:
ORA-12154: TNS:could not resolve service name

Any ideas,  I really your patience and help

Will


>The Cnnect Sring is user defined when you set up your TNSNAMES.ORA file (if
>you take that route).  In the case of a DB link, connect string used is the
>one from TNSNAMES.ORA file on your server, so you have to ensure that
>there's consistency across your system.

>The naming convention we use is Server_Application_Environment, so an
>example would be: DBS1_ACCT_TEST, DBS1_ACCT_PROD etc..



>> can you give an example of a remote conect string??

>> thanks,
>> Will

>> On Thu, 1 Mar 2001 09:56:10 +0100, "Ian Ledzion"

>> >Have you tried a Database link?

>> >CREATE DATABASE LINK my_link USING '<remote_connect_string>';

>> >You can refer to tables in the remote DB using:




>> >> I'm have oracle 8.1 client installed on my NT box and two databases I
>> >> have access to via tcp/ip and mention in tnsnames.ora.  Is there a way
>> >> via sql to access both databases at the same time and do a simple
>> >> join...like  lastname to lastname???

>> >> If not are there ways of  doing this? do I need third party software?

>> >> any help , or directions to the right spot would be greatly
>> >> appreciated.

>> >> regards,
>> >> Will Kramer

 
 
 

Joining tables from two separate oracle databases possible

Post by Robert John Anderse » Sun, 04 Mar 2001 12:35:23




> Ok tried what you said...and here's what I got. I'm still missing something.


> SQL> /

Try taking this instead 'scopus1' or 'scopus1.world'  whichever is in your
tnsnames file.

RJA

 
 
 

Joining tables from two separate oracle databases possible

Post by Juan Mirand » Wed, 07 Mar 2001 04:01:16


Use a database link:

Create public database link Link_Name
Connect to USER identified by PASSW
Using NetAlias_Name

You can see the other databas object in this way:


 
 
 

Joining tables from two separate oracle databases possible

Post by Ed Procha » Wed, 14 Mar 2001 12:40:40


You just need to create a database link to the other instances.
You'll find it documented in the DBA manuals, not in the usual SQL
manuals. They can be very useful at times, thought tricky on security
issues.

HTH
Ed Prochak


> I'm have oracle 8.1 client installed on my NT box and two databases I
> have access to via tcp/ip and mention in tnsnames.ora.  Is there a way
> via sql to access both databases at the same time and do a simple
> join...like  lastname to lastname???

> If not are there ways of  doing this? do I need third party software?

> any help , or directions to the right spot would be greatly
> appreciated.

> regards,
> Will Kramer

 
 
 

Joining tables from two separate oracle databases possible

Post by Shannon St. Denni » Thu, 15 Mar 2001 23:45:41


try either:

create database link my_link2 using 'scopus1';

or

create database link my_link2
connect to mdixxi
identified by mdixxi
using 'scopus1';

then:


the first create statement will cause any connections to connect as the
person running the sql statement
the second will cause any connections to connect as the user identified in
the connect to clause.

Shannon





> > Ok tried what you said...and here's what I got. I'm still missing
something.


> > SQL> /

> Try taking this instead 'scopus1' or 'scopus1.world'  whichever is in your
> tnsnames file.

> RJA

 
 
 

1. Is Joining tables from two separate oracle databases possible?

noticed that my post didn't sound like a question...sooooo here again

I'm have oracle 8.1 client installed on my NT box and two databases I
have access to via tcp/ip and mention in tnsnames.ora.  Is there a way
via sql to access both databases at the same time and do a simple
join...like  lastname to lastname???  

If not are there ways of  doing this? do I need third party software?

any help , or directions to the right spot would be greatly
appreciated.

regards,
Will Kramer

2. How can I determine if an ADO Connection Object has lost it's connection?

3. Joining two tables from two separate Access databases

4. regarding my last post

5. Join tables in TWO databases -- possible??

6. Oracle Export

7. Join two tables from two different SQL databases

8. Getting a AutoIncrement Value

9. SQL Query - join two tables in two different databases

10. inner join query between two table of two different database

11. ...inner join query between two table of two different database

12. Joining two tables from two databases

13. joining two tables from two databases