LewisC's An Expert's Guide To Oracle Technology
This tip relates to the latest version of EnterpriseDB's Advanced Server (AS) product. This is the commercially licensed database that provides Oracle compatibility. I have spoken quite a bit about what that compatibility entails but the gist of it is that the compatibility is code compatibility. The latest version of AS offers quite a few new features that I will be writing about over time. Today I am writing about the Oracle compatible db links.
To understand database links in Oracle, read my entry here on ittoolbox, Database Links: A Definition in Plain English. That entry will give you a good overview of what a link is and what Oracle does with links. This is a hugely useful feature. Pulling data from remote databases can be incredibly important to many applications.
AS provides DDL syntax that is very close to Oracle syntax and DML syntax exactly like Oracle syntax. In addition, you can choose to create links from AS to Oracle or links from AS to other AS instances or even to Postgres instances.
The AS syntax to create a link:
CREATE [ PUBLIC ] DATABASE LINK name
CONNECT TO username IDENTIFIED BY 'password'
USING { libpq 'host=hostname port=portnum dbname=database' |
[ oci ] '//hostname[:portnum]/database' }
Let me break that up just a bit by comparing it to Oracle's syntax:
CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink
[ CONNECT TO
{ CURRENT_USER
| user IDENTIFIED BY password
[ dblink_authentication ]
}
| dblink_authentication
]
[ USING 'connect_string' ] ;
Oracle has an additional keyword, SHARED. This keyword allows multiple users to share a database link. AS does not currently support this syntax.
In both instances, you can choose to create a public or a private link. A public link can be accessed by anyone in a database, a private link can only be accessed by the creator.
dblink is the name of the link. A database link in AS may be any valid name.
In both cases, you define who to connect as and the password for that user. Oracle offers an additional option to connect as CURRENT_USER. AS does not offer that keyword at this time.
In Oracle's syntax, the dblink_authentication is tied to the SHARED database link type.
Both databases have the USING keyword but this is where AS differs most from Oracle.
In AS, when you use USING, you chose to connect to an Oracle database (OCI) or a postgres database (LIBPQ), which would include an AS database. This keyword, OCI or LIBPQ, tells AS which library to use to connect to the remote database. The syntax is mostly the same for both types of database: server, port and database name.
Some examples (I will use EDB*Plus to run the examples):
If I wanted to create a private database link from my local AS server to an AS database (in this case, the same database), I would use this syntax:
CREATE DATABASE LINK aLocalAsDb
CONNECT TO enterprisedb IDENTIFIED BY edb
USING libpq 'host=localhost port=5444 dbname=edb'
Connected to EnterpriseDB 8.3.0.12 (localhost:5444/edb) AS enterprisedb
EDB*Plus: Release 8.3 (Build 14)
Copyright (c) 2008, EnterpriseDB Corporation. All rights reserved.
SQL> drop database link alocalasdb;
Database Link dropped.
SQL> CREATE DATABASE LINK aLocalAsDb
2 CONNECT TO enterprisedb IDENTIFIED BY edb
3 USING libpq 'host=localhost port=5444 dbname=edb'
4 /
Database Link created.
SQL> select 123 from dual@alocalasdb;
?COLUMN?
-----------
123
SQL>
Creating and using a link to Oracle is very similar. I have an Oracle XE database. I will create a public link that connects to the HR user. I would use this syntax:
EDB*Plus: Release 8.3 (Build 14)
Copyright (c) 2008, EnterpriseDB Corporation. All rights reserved.
SQL> conn enterprisedb/edb
Connected to EnterpriseDB 8.3.0.12 (localhost:5444/edb) AS enterprisedb
SQL> create public database link oraxe
2 connect to hr identified by hr
3 using oci '//localhost:1521/xe';
Database Link created.
SQL> select 123 from dual@oraxe;
?COLUMN?
-----------
123
SQL> select count(*) from employees@oraxe;
COUNT
--------------------
107
SQL>
And that's just about it. At this time, you cannot use a database link to call a remote procedure. That is planned for the future.
Also, if you connect to an 11g database, you may need to quote your password if you use a mixed case password in Oracle. 11g allows users to have mixed case passwords.
Because AS is using OCI to connect to the Oracle database, you must have an Oracle client installed and correctly configured on the AS server to create a database link to Oracle. You may use the instant client or the full client. Your ORACLE_HOME must also be correctly set. It can also be set in an AS configuration file.
Take care,
LewisC
