snippetModerate

How to connect to another db using stored procedures?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

storedproceduresconnectusinganotherhow

Problem

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.

Solution

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.

Code Snippets

-- 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;

Context

StackExchange Database Administrators Q#185, answer score: 10

Revisions (0)

No revisions yet.