Re: Help with query across schema in TOAD

zwasdl_at_gmail.com schreef:

> Please see my clarification below.
>
> Thanks,
>
> Wei
>
> Frank van Bortel wrote:
> > zwasdl_at_gmail.com schreef:
> > > Hi,
> > > I think I didn't describe my questions properly.
> > > I have two userIDs which can connect to two different Oracle
> > > Databases.
> >
> > Misconception #1: it is one database, two schema's!
>
> I am accessing two databases at two different locations with different
> IP addresses.
>
> >
> > > I'm trying to join the tables from the two databases. I know
> > > in MS Access I can do that via linked tables, but I can't write hint in
> > > query. So I have the following two questions:
> > > 1. How can I do it in TOAD/Oracle?
> > Not. Unless you grant access to the tables, owned by A to B:
> > grant select on my_table to b;
> >
> > B can now: select * from a.mytable;
>
> I assume one Oracle database can't grant access to another database,
> right?
> Can I use connection string to join tables in two database in one
> query?
> Microsoft Access creates local linked tables (with userid and password
> and tnsname defined) which map to remote tables via ODBC, and therefore
> it is not restricted to one Oracle database.
>
> >
> > > 2. How can I add hint in Access in this case? I know pass-through
> > > query might work, but will face the same issue as in question 1.
> >
> > Would not know. What is Access?
>
> Microsoft Access
>
> >
> > --
> > Regards,
> > Frank van Bortel
> >
> > Top-posting is one way to shut me up...

Looks like you'll need database links. Create a database link (let's say it's called link1) and perform queries like select a.id, b.name from departments a, employees_at_link1 b
where a.id = b.dept_id

The user account for the table b is defined in the database link.

Robbert van der Hoorn
OSA it BV
The Netherlands Received on Tue Oct 03 2006 - 15:49:53 CDT