Prerequisites:
- Created EnterpriseDB PostgreSQL Advanced Server (EPAS) cluster from BigAnimal. This document shows how to create the cluster.
- The superuser privilege is granted. This document shows how to get the superuser privilege.
- Default Oracle server port number is 1521. If the port number is not the default number, additional port mapping is required.
- If the Oracle server is on a private network, prior connectivity from the BigAnimal subnet to the Oracle server network must be established.
Procedure:
Step 1. Connect to BigAnimal cluster.
Step 2. Establish a connection to an Oracle database with user-specified connection information
SELECT dblink_ora_connect(‘<conn_name>’, ‘<server_name>’, ‘<service_name>’, ‘<user_name>’, ‘<password>’, ‘<port>’);
|
edb_admin=# SELECT dblink_ora_connect('conn_name', 'server_name', 'service_name', 'user_name', 'password', ’port’); dblink_ora_connect ------------------- OK (1 row) |
The example above does not leave a persistent database link behind. The link exists only during the user’s session.
Step 3. Check the dblink and test connection.
SELECT dblink_ora_status(‘<conn_name>’);
|
edb_admin=# SELECT dblink_ora_status('conn_name'; dblink_ora_status ------------------- OK (1 row) |
Step 4. Once complete, disconnect the connection via the dblink_ora_disconnect(‘<conn_name>’) function or the connection will disconnect automatically when the user session has been terminated.
Note(s):
- Calling a database link from an EPAS cluster using ‘dblink_ora’ functions and procedures does not require the installation of an extension. The EnterpriseDB Foreign Data Wrapper for Oracle extension 'edb_dblink_oci' is already installed in EPAS thereby providing access to ‘dblink_ora’ functions.