Re: query to join two tables in 2 different Oracle schema

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
//
//
Received on Thu Oct 07 2004 - 14:55:19 CDT