Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Run a script on TOAD SQL editor that pulls data from several DBs

Posted on 2007-11-17
7
4,165 Views
Last Modified: 2013-12-07
I've opened several connections to various dbs in toad. i was trying to write a pl/sql in an editor session opened on one oracle db connection and would like to incorporate data from an oracle db on another connection.
Example:
select db1field
from db1.db1table1
where  fieldA = (select max(db2field) from db2.db2table)

When i run it i get - "table or view does not exist". Is there a sytanx/method for such things?
Thanks!

0
Comment
Question by:Algorithmix
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20305509
assuming that db1 and db2 where database links, in oracle you specify them like this:

select db1field
from db1table1@db1 
where  fieldA = (select max(db2field) from db2table@db2)

Open in new window

0
 
LVL 8

Expert Comment

by:LindaC
ID: 20305837
Also if you have created your database link with another schema that is not the owner of the table and has been granted select privilege on that table but there is no synonym, then you must specify the owner of the table like this:

select dblfield from owner.dbltable@db1 where filedA = (select max(db2field) from owner.db2table@db2

Otherwise in the remote db you must grant select privilege on the table and also create a private synonym or a public synonym.
0
 
LVL 11

Accepted Solution

by:
Akenathon earned 500 total points
ID: 20306165
You cannot make one connection use data from another connection, it's not TOAD's limitation, you cannot do that with any DB connection.

Supposing you are connected to db1, the syntax you are looking for is down there, where:
- "public" means anybody can use the connection to db2. If not specified, it's private to the username you are connected with
- connect to etc. describes the username/password to connect to db2. If not specified, the same username and password that you used to connect to db1 in the first place will be tried for db2
- db2_tnsnames_alias is the tnsnames.ora entry name, i.e. what you put to connect to db2: sqlplus username/password@db2_tnsnames_alias

create [public] database link db2
[connect to <db2username> identified by <db2password>] using 'db2_tnsnames_alias';
 
select db1field
from db1.db1table1 
where  fieldA = (select max(db2field) from db2table@db2);

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Algorithmix
ID: 20306799
That's surely the way to do it Akenathon but I guess I don't have creat link privs. Is there a script I can write to list available public links..if any?
Thanks for your solution.
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 20307239
Sure, look at all_db_links to see the ones you can use, and dba_db_links to see all of them. You need to have CREATE DATABASE LINK and/or CREATE PUBLIC DATABASE LINK privileges to add to the list :-)
0
 

Author Comment

by:Algorithmix
ID: 20308629
Great thanks...err...how would I go about viewing the tnsnames.ora?
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 20309802
You'll need to look below $ORACLE_HOME/network/admin (net80/admin if you're on v8), where $ORACLE_HOME stands for "wherever it is that you placed the installation". Just perform an OS find, you can't miss it...

Now, you can avoid messing with it: just use the network configuration assistant, it will let you look and update the thing in a supposedly nicer GUI environment... and TOAD itself has a "tnsnames editor" too!

Friendly advice: if you intend to mess with Oracle any further, read through the concepts manual as soon as you possibly can ;-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question