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.
Im using Oracle and both databases are in the same server.
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 only takes a minute to sign up.Sign up to join this community
You want to use a Database Link (DBLink).
-- 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.