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.
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 communityYou 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.
yes, you can, you can read data from about any database using dg4odbc. Any odbc complient database can be connect to and Oracle database providing you can find a working odbc driver for out platform. .odbc.ini and $ORACLE_HOME/hs/admin are your configuration locations.