Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 8 down vote favorite

I need to get some data from an external db and make some calculations with it in another db, is it possible to connect to an external db from a stored procedure? Thanks guys.

PS.

Im using Oracle and both databases are in the same server.

share|improve this question

1 Answer

up vote 10 down vote accepted

You want to use a Database Link (DBLink).

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

http://psoug.org/reference/db_link.html

-- create tnsnames entry for conn_link
conn_link =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = perrito2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orabase)
    )
  )


CREATE DATABASE LINK conn_user
USING 'conn_link';

SELECT table_name, tablespace_name FROM user_tables@conn_user;

@conn_user on the end of a procedure or table name will tell the pl/sql engine to query the db link specified for that info.

share|improve this answer
1  
Embedded deep within your PSOUG link is a reference to the DRIVING_SITE hint - performance of queries across DBLINKs can vary greatly - if you KNOW where most of the data processing is going to be done, be sure to set the DRIVING_SITE to the database where the data processing will be most efficient. – ScottCher Jan 4 '11 at 20:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.