Home » SQL & PL/SQL » SQL & PL/SQL » How to Select Data From Two different Databases?
icon7.gif  How to Select Data From Two different Databases? [message #223438] Thu, 08 March 2007 22:29 Go to next message
raje.vinayak
Messages: 10
Registered: January 2007
Location: thane
Junior Member
Hi All,

I want to Select data from Two different Databases

e.g
there is a Table Emp(50 rows) in Database Db1
and
there is also a Table Emp(100 rows) in Database Db2

how can i check which 50 rows are not there in Emp of dbase Db1 ?
How can i insert those 50 rows in Emp of dbase Db1

Waiting for u r Kind Help and Suggestions

Thanking you

vinayak
Re: How to Select Data From Two different Databases? [message #223454 is a reply to message #223438] Fri, 09 March 2007 00:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use a database link (or dblink).
Re: How to Select Data From Two different Databases? [message #223459 is a reply to message #223454] Fri, 09 March 2007 00:28 Go to previous messageGo to next message
raje.vinayak
Messages: 10
Registered: January 2007
Location: thane
Junior Member
Hi

Thanx Frank

i know that i ve to use database link
bt how?
i dont ve knowledge of database link.

can u plz give source code for this includeing dblink creation and all that?

waiting for replay
Re: How to Select Data From Two different Databases? [message #223460 is a reply to message #223459] Fri, 09 March 2007 00:33 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_5005.htm

Babu
Re: How to Select Data From Two different Databases? [message #223463 is a reply to message #223459] Fri, 09 March 2007 00:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you try to google for it? oracle dblink returns 104000 hits...
Re: How to Select Data From Two different Databases? [message #223471 is a reply to message #223463] Fri, 09 March 2007 01:12 Go to previous messageGo to next message
raje.vinayak
Messages: 10
Registered: January 2007
Location: thane
Junior Member
Hello Frank

thanks for suggestion

bt if i had time to search on google i had done that
i m in office
cant access google and other sites
only this site is given access with

so it ws urgent that's why i asked for the help

anyway thanks for u r kind suggestions
Re: How to Select Data From Two different Databases? [message #223472 is a reply to message #223471] Fri, 09 March 2007 01:15 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

To propagate event messages between systems, you must create database links from your local system to the remote systems. You should fully qualify the database link name with the domain name.

You can either create database links manually, or use Oracle DBA Studio in the Oracle Enterprise Manager to perform this step. Oracle DBA Studio allows workflow administrators to quickly and easily create and administer database links, queue tables, queues, and queue propagation without requiring knowledge of the SQL DDL commands. See: DBA Management Pack, Oracle Enterprise Manager Administrator's Guide.

You can use the following syntax to create a database link manually:

CREATE DATABASE LINK <database link name> CONNECT TO
<user> IDENTIFIED BY <password>
USING '<connect string>';

For example:

CREATE DATABASE LINK wf817.us.oracle.com CONNECT TO
wfuser IDENTIFIED BY welcome
USING 'wf817';

If you have multiple installations of Oracle Workflow on both the local database and the remote database, and you want to use the same username and password to access both systems, you can omit the <user> IDENTIFIED BY <password> clause. In this case, the database link uses the username and password of the user who is connected to the database.

CREATE DATABASE LINK <database link name> CONNECT TO
USING '<connect string>';

If you want to create a public database link available to all users, specify the parameter PUBLIC.

CREATE PUBLIC DATABASE LINK <database link name> CONNECT TO
<user> IDENTIFIED BY <password>
USING '<connect string>';

To verify the names of your database links, use the following syntax:

SELECT db_link FROM all_db_links
Re: How to Select Data From Two different Databases? [message #223479 is a reply to message #223471] Fri, 09 March 2007 02:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
raje.vinayak wrote on Fri, 09 March 2007 08:12

i m in office
cant access google and other sites
only this site is given access with



Sorry, but I find it quite hard to believe that some company prohibits access to the entire internet but this very site...
You have no access to the Oracle documentation either then?
Re: How to Select Data From Two different Databases? [message #223491 is a reply to message #223479] Fri, 09 March 2007 03:06 Go to previous messageGo to next message
raje.vinayak
Messages: 10
Registered: January 2007
Location: thane
Junior Member
Hello gentlebabu

Thank u very very much for u r quick Help Gentlebabu


vinayak
Re: How to Select Data From Two different Databases? [message #223496 is a reply to message #223479] Fri, 09 March 2007 03:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I find that a little hard to believe as well.

Can you see this
Re: How to Select Data From Two different Databases? [message #223499 is a reply to message #223491] Fri, 09 March 2007 03:26 Go to previous message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Hi,

Did you solved your problem ?

Babu
Previous Topic: How to create a directory (merged)
Next Topic: Exlain Plan for Group By Clause
Goto Forum:
  


Current Time: Thu Dec 08 16:12:33 CST 2016

Total time taken to generate the page: 0.08127 seconds