query to join two tables in 2 different Oracle schema

[Harry]

[x-post to c.l.p.m]

At my work we have an Oracle 9 database sitting on a W2K server.
Therre are two different schema, one for overall info and the other
just for storing reports.

schema A: a table "study" with study_id, etc.

schema B: a table "report" with study_id, etc.

How can I made a query, in SQL Plus (or PL/SQL, or Perl script)
that can join the table together?

pseduo code:

select s.study_id, r.report_type
from study s connected to schema A using userid xxx password yyy,
report r connected to schema B using userid ppp password qqq
where s.study_id = r.study_id

Is it doable?

TIA

 

[Klaus Byskov Pedersen]

So you have two schema's A and B right? And in each there is a table that
you want to use in a join.
study is in schema A owned by some user, A_USER.
report is in schema B owned by some user, B_USER.

You have a user that you want to able to make this join. Lets call the
user ANDREW.

so in SQL-plus you grant ANDREW the right to select on both these tables.

log in as A_USER.

grant select on A.study to ANDREW;

log in as B_USER

grant select on B.report to ANDREW;

log in as ANDREW

select s.study_id, r.repoty_type
from A.study s, B.report r
where s.study_id = r.study_id;

And that should be it!

If the tables are in different DATABASES (where you actually have to log
in) you might want to look at the CREATE DATABASE LINK command:

https://cwisdb.cc.kuleuven.ac.be/ora10doc/server.101/b10759/statements_5005.htm

Hope it helps you!

/Klaus

On Thu, 7 Oct 2004, Harry wrote:

//[x-post to c.l.p.m]
//
//At my work we have an Oracle 9 database sitting on a W2K server.
//Therre are two different schema, one for overall info and the other
//just for storing reports.
//
//schema A: a table "study" with study_id, etc.
//
//schema B: a table "report" with study_id, etc.
//
//How can I made a query, in SQL Plus (or PL/SQL, or Perl script)
//that can join the table together?
//
//pseduo code:
//
// select s.study_id, r.report_type
// from study s connected to schema A using userid xxx password yyy,
// report r connected to schema B using userid ppp password qqq
// where s.study_id = r.study_id
//
//Is it doable?
//
//TIA
//
//

 

[Gerhard M]

schema B: a table "report" with study_id, etc.

select s.study_id, r.report_type
from study s connected to schema A using userid xxx password yyy,
report r connected to schema B using userid ppp password qqq
where s.study_id = r.study_id

as study_id is within table report try
SQL> select study_id, report_type from report;

gerhard

.... but that's not a post for comp.lang.perl.misc, so please check
groups before posting ...

 

[Harry]

Klaus Byskov Pedersen wrote...

Yeah, this is the trick; it's working.
I have learned the "grant privileges" command before; just forgot
to apply this in my case.

Thanks