Table of Contents Previous Next


3 The SQL Language : 3.3 SQL Commands : 3.3.13 CREATE [PUBLIC] DATABASE LINK

CREATE [PUBLIC] DATABASE LINK -- create a new database link.
username IDENTIFIED BY ‘password’}
USING { libpq 'libpq_connection_string' |
[ oci ] 'oracle_connection_string' }
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.
For Advanced Server 9.4, the CREATE DATABASE LINK command is tested against and certified for use with Oracle version 10g Release 2 (10.2) and Oracle version 11g Release 2 (11.2).
Include CURRENT_USER to specify that Advanced Server should use the user mapping associated with the role that is using the link when establishing a connection to the remote server.
Specifies a libpq connection to a remote Postgres Plus Advanced Server database.
libpq_connection_string
Specifies a connection to a remote Oracle database. This is Advanced Server’s default behavior.
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. Set the LD_LIBRARY_PATH environment variable on Linux (or PATH on Windows) to the lib directory of the Oracle client installation directory.
For Windows only, you can instead 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 Windows PATH environment variable.
The LD_LIBRARY_PATH environment variable on Linux (PATH environment variable or oracle_home configuration parameter on Windows) must be set properly each time you start Advanced Server.
For Windows only: To set the oracle_home configuration parameter in the postgresql.conf file, edit the file, adding the following line:
oracle_home = 'lib_directory '
After setting the oracle_home configuration parameter, you must restart the server for the changes to take effect. Restart the server from the Windows Services console.
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.
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.
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.
Issue a SELECT command on the emp table in the Oracle database using database link, oralink.
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.
Display attributes of database links, oralink and edblink, from the local edb_dblink system table:
Perform a join of the emp table from the Oracle database with the dept table from the Postgres Plus Advanced Server database:

3 The SQL Language : 3.3 SQL Commands : 3.3.13 CREATE [PUBLIC] DATABASE LINK

Table of Contents Previous Next