Postgres Plus Advanced Server Oracle Compatibility Developer's Guide :

Previous PageTable Of ContentsNext Page

3.3.11 CREATE [PUBLIC] DATABASE LINK

Name

CREATE [PUBLIC] DATABASE LINK -- create a new database link.

Synopsis

CREATE [ PUBLIC ] DATABASE LINK name
  CONNECT TO { CURRENT_USER | 
               username IDENTIFIED BY ‘password’}
  USING { libpq 'libpq_connection_string' |
        [ oci ] 'oracle_connection_string' }

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.

username

The username to be used for connecting to the remote database.

CURRENT_USER

      Include CURRENT_USER to specify that Advanced Server should use the user name and password of the link owner to establish a connection to the remote server.

password

The password for username.

libpq

Specifies a libpq connection to a remote Postgres Plus Advanced Server database.

libpq_connection_string

      Specify the connection information for a libpq connection.

oci

Specifies a connection to a remote Oracle database. This is Advanced Server’s default behavior.

oracle_connection_string

Specify the connection information for an oci connection.

Notes

To create a non-public database link you must have the CREATE DATABASE LINK privilege. To create a public database link you must have the CREATE PUBLIC DATABASE LINK privilege.

If you are executing a SQL command that references a database link to a remote Oracle database, Advanced Server needs a way to know where the correct Oracle installation resides on disk. You can either set the LD_LIBRARY_PATH environment variable (or PATH on Windows) to the lib directory of the Oracle client installation directory or set the value of the oracle_home configuration parameter in the postgresql.conf file. The value specified in the oracle_home configuration parameter will override the LD_LIBRARY_PATH (or PATH on Windows) environment variable.

The LD_LIBRARY_PATH (or PATH on Windows) environment variable must be set properly each time you start Advanced Server. To set the oracle_home configuration parameter in the postgresql.conf file, edit the file, adding the following line:

      oracle_home = 'lib_directory '

Substitute the name of the directory that contains libclntsh.so (on Linux) or oci.dll (on Windows) for lib_directory.

After setting the oracle_home configuration parameter, you must restart the server for the changes to take effect. Restart the server by executing the following command:

    /etc/init.d/ppas-9.1 restart

Examples

Creating an oci-dblink Database Link

The following example demonstrates using the CREATE DATABASE LINK command to create a database link (named chicago) that connects an instance of Advanced Server to an Oracle server via an oci-dblink connection. The connection information tells Advanced Server to log in to Oracle as user admin, whose password is mypassword. Including the oci option tells Advanced Server that this is an oci-dblink connection; the connection string, '//127.0.0.1/acctg' specifies the server address and name of the database.

      CREATE DATABASE LINK chicago

      CONNECT TO admin IDENTIFIED BY 'mypassword'

      USING oci '//127.0.0.1/acctg';

Note: You can specify a hostname in the connection string (in place of an IP address).

Creating a libpq Database Link

The following example demonstrates using the CREATE DATABASE LINK command to create a database link (named boston) that connects an instance of Advanced Server to a Postgres Server via a libpq connection. The connection information tells Advanced Server to log in to Postgres as user admin, whose password is mypassword. Including the libpq option tells Advanced Server that this is a libpq connection; the connection string, 'host=127.0.0.1 dbname=sales' specifies the server address and name of the database.

      CREATE DATABASE LINK boston

      CONNECT TO admin IDENTIFIED BY 'mypassword'

      USING libpq 'host=127.0.0.1 dbname=sales';

Note: You can specify a hostname in the connection string (in place of an IP address).

Using a Database Link

The following examples demonstrate using a database link with Advanced Server to connect to an Oracle database. The 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

DROP DATABASE LINK

Previous PageTable Of ContentsNext Page