Oracle Database Links: Querying Across Databases in PL/SQL

Connecting to Remote Databases

Database links let you query tables and execute procedures on remote Oracle databases as if they were local. For APEX applications that need to integrate data from multiple databases, such as pulling HR data from one database and financial data from another, database links provide transparent access without building REST APIs or ETL processes.

Creating a Database Link

CREATE DATABASE LINK finance_db
    CONNECT TO finance_reader IDENTIFIED BY "SecurePassword123"
    USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=findb.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=finprod)))';

-- Or using a TNS name
CREATE DATABASE LINK finance_db
    CONNECT TO finance_reader IDENTIFIED BY "SecurePassword123"
    USING 'FINPROD';

Querying Across the Link

-- Simple remote query
SELECT invoice_id, amount, status
  FROM invoices@finance_db
 WHERE customer_id = :P10_CUSTOMER_ID;

-- Join local and remote tables
SELECT e.first_name, e.last_name, p.total_compensation
  FROM employees e
  JOIN payroll_summary@finance_db p ON e.employee_id = p.employee_id
 WHERE e.department_id = :P10_DEPT_ID;

-- Call a remote procedure
BEGIN
    finance_pkg.post_journal_entry@finance_db(
        p_amount  => :P10_AMOUNT,
        p_account => :P10_ACCOUNT
    );
END;

Performance Considerations

Remote queries are inherently slower than local queries due to network latency. The optimizer’s ability to push predicates to the remote database affects performance dramatically. Use the DRIVING_SITE hint to control which database drives the join:

-- Force the join to execute on the remote database
SELECT /*+ DRIVING_SITE(p) */
       e.first_name, p.total_compensation
  FROM employees e
  JOIN payroll_summary@finance_db p ON e.employee_id = p.employee_id;

For frequently accessed remote data, consider creating a local materialized view that refreshes periodically rather than querying across the link for every page load.

Security

Database link credentials are stored in the data dictionary. Use a dedicated read-only account on the remote database with the minimum required privileges. Consider using Oracle Wallet for credential storage instead of embedding passwords in the CREATE DATABASE LINK statement. Review database links periodically and drop any that are no longer needed.

Limitations

Some operations are restricted across database links. You cannot use remote LOBs directly in local PL/SQL. Distributed transactions (DML on multiple databases in one transaction) require two-phase commit and can be complex to manage. Remote DDL is not supported. For complex cross-database workflows, consider using APEX_WEB_SERVICE with REST APIs as an alternative architecture.