Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2014
    Posts
    3

    Unanswered: Joining 2 tables from different databases

    Hi all,

    My goal here is to join data from 2 tables that reside on separate databases located within the company network. My current query is:
    Code:
    select * from dbName.schema.table c
    left join dbName2.schema2.table2 ag
    on c.column = ag.column
    I am using the TOAD for Oracle software (first time ever), and the error I get is "ORA-00933: SQL command not properly ended".

    I tried just
    Code:
    select * from dbName.schema.table
    from within the dbName connection just to see if the fully-qualified table name works, and it fails.

    The database tabs say <user>@<db>.WORLD. What does the world mean?

    This is my first time using TOAD, although I do have quite a bit of SQL Developer and SQL Server experience (so SQL isn't an issue).

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You can only be connected to one database at a time. You will need to define a database link pointing to another database, then reference it in your query. You'll find plenty examples of database links (dblinks) on the interwebs.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Apr 2014
    Posts
    3
    Quote Originally Posted by n_i View Post
    You can only be connected to one database at a time. You will need to define a database link pointing to another database, then reference it in your query. You'll find plenty examples of database links (dblinks) on the interwebs.
    Thanks, I will look into dblinks.

    Meanwhile, do you know why even a simple select statement with the fully qualified table name fails and returns "table not found"? The query is:
    Code:
    select * from [dbName.world].[schema1].tableName
    Not only does this fail with the aforementioned error, Toad for Oracle shows a red underline (syntax error?) for the [ and the . between dbName and world.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    May be you should use Oracle syntax when querying an Oracle database? Square brackets are an MS SQL Server artefact, and the database name is not a part of fully qualified name in Oracle either.
    Last edited by n_i; 04-28-14 at 21:37.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by n_i View Post
    May be you should use Oracle syntax when querying an Oracle database?
    Where do you get these radical ideas?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Apr 2014
    Posts
    3
    Quote Originally Posted by n_i View Post
    May be you should use Oracle syntax when querying an Oracle database? Square brackets are an MS SQL Server artefact, and the database name is a part of fully qualified name in Oracle either.
    Oh I didn't know that was a MSSQL thing. How do you separate parts of the qualified name into specific groups, i.e. if dbName.world was the name of the database, how would I prevent the execution from thinking world is a table in dbName?

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The ONLY way one database can access another one is through a database link. For example

    select dummy from dual;

    would select from your database

    select dummy from dual@dbname2;

    would select from the database which is connect via the dbname2 database link
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •