query to join two tables in 2 different Oracle schema

Discussion in 'Perl Misc' started by Harry, Oct 7, 2004.

  1. Harry

    Harry Guest

    [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
     
    Harry, Oct 7, 2004
    #1
    1. Advertisements

  2. 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.
    log in as B_USER
    log in as ANDREW
    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
    //
    //
     
    Klaus Byskov Pedersen, Oct 7, 2004
    #2
    1. Advertisements

  3. Harry

    Gerhard M Guest


    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 ...
     
    Gerhard M, Oct 8, 2004
    #3
  4. Harry

    Harry Guest

    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
     
    Harry, Oct 8, 2004
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.