How
to connect to different database?
I am looking for a way, within
a procedure, to select data from an Oracle table in a different database.
Is there a special connect string I can employ to make this connection?
Once I connect, do I need to use views to generate the data set?
I am fairly new to Oracle, so any assistance will be greatly appreciated.
Thanks!
You have to create Database
Link for this purpose. To take the advantage of Database Link you must
install SQL*Net in your computer. You will get valid Sqlnet_String
from SQL*Net.
The syntax for creating Database
Link is
CREATE DATABASE LINK link_name
CONNECT TO username IDENTIFIED
BY passd
USING Sqlnet_String
Then you can use this database
link in your SQL statement
as TABLENAME@Link_Name
I will suggest to create a
synonym of table using that database link and then use those synonym. So
that you don't have to use @Link_Name everytime you are refering the table
name in the SQL statement.
Best Regards.
Alam
Ans2:
You will first need to create
a database link to the remote server. Hopefully a TNSNAMES
service entry has been established for the remoter database.
This will allow you to add @ as a suffix to the remote database table name.
For example tableremote@remotestring. Once the
database link is created a synonym can created as well.
You will only have access to the objects that the user who is identified
in the database link. Check manuals for more detailed and syntax.
Good Luck and Best Regards