Oracle database link syntax

Oracle database link syntax allows you to connect with a remote database, table, or view. This capability does not apply to the Entry plan of the Db2® Warehouse on Cloud managed service.

The database link syntax uses the @ (at sign) to indicate an in or membership condition. For example, to access a remote object pencils under schema user using a database link to stock, you can use:

SELECT * FROM user.pencils@stock;

Note: The @ character is supported as a valid character in an ordinary identifier. For example, you can create a table with pencils@stock as its name. When database link support is enabled, the @ character is treated as a special delimiter in table, view, and column references. If you want to use @ in database object names when link support is enabled, you must enclose the name with double quotes.

Examples

Remote object references are formatted as:

<schema_name>,<object_name>@<server_name>

Column references can also be included:

<schema_name>,<object_name>,<column_name>@<server_name>

The following SELECT statements query a remote table named EMPLOYEE:

SELECT birthdate FROM rschema.employee@sudb WHERE firstname='SAM'
SELECT rschema.employee.birthdate@sudb FROM rschema.employee@sudb 
   WHERE rschema.employee.firstname@sudb ='SAM'

You can also issue UPDATE, INSERT, and DELETE statements against a remote table:

UPDATE rschema.employee@sudb SET firstname='MARY'
INSERT INTO rschema.employee@sudb VALUES ('Bob')
DELETE FROM rschema.employee@sudb