Name
CREATE DATABASE LINK -- create a new database link
Synopsis
CREATE [ PUBLIC ] DATABASE LINK name CONNECT TO username IDENTIFIED BY ‘password’ USING { libpq ‘host=hostname port=portnum dbname=database’ | [ oci ] ‘//hostname[:portnum]/database’ }
Description
CREATE DATABASE LINK creates a new database link. A database link is an object that allows a reference to a table or view in a remote database within a DELETE, INSERT, SELECT, or UPDATE command. A database link is referenced by appending @dblink to the table or view name referenced in the SQL command where dblink is the name of the database link.
Database links can be public or private. A public database link is one that can be used by any user. A private database link can be used only by the database link’s owner. Specification of the PUBLIC option creates a public database link. If omitted, a private database link is created.
When the CREATE DATABASE LINK command is given, the database link name and the given connection attributes are stored in the Postgres Plus Advanced Server system table named, pg_catalog.edb_dblink. When using a given database link, the database containing the edb_dblink entry defining this database link is called the local database. The server and database whose connection attributes are defined within the edb_dblink entry is called the remote database.
A SQL command containing a reference to a database link must be issued while connected to the local database. When the SQL command is executed, the appropriate authentication and connection is made to the remote database to access the table or view to which the @dblink reference is appended.
Parameters
PUBLIC
Create a public database link that can be used by any user. If omitted, then the database link is private and can only be used by the database link’s owner.
name
The name of the database link to be created.
username
The username to be used for connecting to the remote database.
password
The password for username.
libpq
Specify connection to a remote Postgres Plus Advanced Server database.
oci
Specify connection to a remote Oracle database. This is the default if omitted.
hostname
Name or IP address of the server hosting the remote database.
portnum
Port number accepting connections to the remote database server.
database
The remote database name.
Notes
If a SQL command is to be executed that references a database link to a remote Oracle database, the server needs some way to know where the correct Oracle installation resides on disk. There are two ways to point Postgres Plus Advanced Server to the correct Oracle installation home directory upon start up:
● The environment variable, ORACLE_HOME, may be set to the correct directory. This is the default Oracle configuration.
● The postgresql.conf configuration parameter oracle_home will also direct Postgres Plus Advanced Server to the correct Oracle Home directory in the file system. See Section 1.3.4 for information on oracle_home.
Examples
The following examples assume that a copy of the Postgres Plus Advanced Server sample application’s emp table has been created in an Oracle database and a second Postgres Plus Advanced Server database cluster with the sample application is accepting connections at port 5443.
Create a public database link named, oralink, to an Oracle database named, xe, located at 127.0.0.1 on port 1521. Connect to the Oracle database with username, edb, and password, password.
CREATE PUBLIC DATABASE LINK oralink CONNECT TO edb IDENTIFIED BY 'password' USING '//127.0.0.1:1521/xe';
Issue a SELECT command on the emp table in the Oracle database using database link, oralink.
SELECT * FROM emp@oralink; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+--------------------+------+------+-------- 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800 | | 20 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600 | 300 | 30 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250 | 500 | 30 7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975 | | 20 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250 | 1400 | 30 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850 | | 30 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450 | | 10 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000 | | 20 7839 | KING | PRESIDENT | | 17-NOV-81 00:00:00 | 5000 | | 10 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500 | 0 | 30 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100 | | 20 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950 | | 30 7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000 | | 20 7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300 | | 10 (14 rows)
Create a private database link named, edblink, to a Postgres Plus Advanced Server database named, edb, located on localhost, running on port 5443. Connect to the Postgres Plus Advanced Server database with username, enterprisedb, and password, password.
CREATE DATABASE LINK edblink CONNECT TO enterprisedb IDENTIFIED BY 'password' USING libpq 'host=localhost port=5443 dbname=edb';
Display attributes of database links, oralink and edblink, from the local edb_dblink system table:
SELECT lnkname, lnkuser, lnkconnstr FROM pg_catalog.edb_dblink; lnkname | lnkuser | lnkconnstr ---------+--------------+------------------------------------- oralink | edb | //127.0.0.1:1521/xe edblink | enterprisedb | host=localhost port=5443 dbname=edb (2 rows)
Perform a join of the emp table from the Oracle database with the dept table from the Postgres Plus Advanced Server database:
SELECT d.deptno, d.dname, e.empno, e.ename, e.job, e.sal, e.comm FROM emp@oralink e, dept@edblink d WHERE e.deptno = d.deptno ORDER BY 1, 3; deptno | dname | empno | ename | job | sal | comm --------+------------+-------+--------+-----------+------+------ 10 | ACCOUNTING | 7782 | CLARK | MANAGER | 2450 | 10 | ACCOUNTING | 7839 | KING | PRESIDENT | 5000 | 10 | ACCOUNTING | 7934 | MILLER | CLERK | 1300 | 20 | RESEARCH | 7369 | SMITH | CLERK | 800 | 20 | RESEARCH | 7566 | JONES | MANAGER | 2975 | 20 | RESEARCH | 7788 | SCOTT | ANALYST | 3000 | 20 | RESEARCH | 7876 | ADAMS | CLERK | 1100 | 20 | RESEARCH | 7902 | FORD | ANALYST | 3000 | 30 | SALES | 7499 | ALLEN | SALESMAN | 1600 | 300 30 | SALES | 7521 | WARD | SALESMAN | 1250 | 500 30 | SALES | 7654 | MARTIN | SALESMAN | 1250 | 1400 30 | SALES | 7698 | BLAKE | MANAGER | 2850 | 30 | SALES | 7844 | TURNER | SALESMAN | 1500 | 0 30 | SALES | 7900 | JAMES | CLERK | 950 | (14 rows)
See Also
