| Multiple DBs [message #1662] |
Mon, 20 May 2002 06:53  |
Mike Nibeck
Messages: 49
Registered: May 2002
|
Member |
|
|
New to Oracle, but 10+ years expoerience in app development and other DB environments.
Problem: We have a series of stored procs that need to query DB #1, which we don't own or have anything other than READ access to, and based on finding set flags/data in DB #2, which is created in-house and we have full access to.
Question: How do we establich the connection to 2 databases at the same time? Should this be done in the stored proc or external to it? If we did need to perform a transaction across both DBs, how do we control things like multi-phase commit?
I've got lots more questions, but I'll start here ;-)
Thanks,
Mike
|
|
|
|
| Re: Multiple DBs [message #1670 is a reply to message #1662] |
Tue, 21 May 2002 07:25  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
You need to create a database link between the two dbs.
You will need:
1) Access to an account on the remote Db
2) CREATE SESSION priv. on the remote Db.
3) Either CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK privileges on the local Db
The basic syntax is
CREATE DATABASE LINK link_name CONNECT TO remote_username IDENTIFIED BY remote_password USING connect_string
Where connect_string specifies the service name of the remote db.
Once you've got the link, you can access tables on the remote Db by apppending @link_name to the tables in the query.
Eg
Select namefrom emp@link_name
where gender = 'Other';
Hope this helps.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
