Very Computer

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

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

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

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

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

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

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

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:

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

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

Very Computer

Joining tables from two separate oracle databases possible

Joining tables from two separate oracle databases possible

Joining tables from two separate oracle databases possible

Joining tables from two separate oracle databases possible

Joining tables from two separate oracle databases possible

Joining tables from two separate oracle databases possible

Joining tables from two separate oracle databases possible

Joining tables from two separate oracle databases possible

Joining tables from two separate oracle databases possible

Joining tables from two separate oracle databases possible

Joining tables from two separate oracle databases possible