Oracle APEX allows you to access a target database from your application, enabling seamless integration between different database environments. This can be useful when retrieving data, updating records, or performing transactions on an external database. There are multiple methods to achieve this, including Database Links, RESTful Web Services, and Oracle Autonomous Database connectivity.
Using a Database Link to Access a Target Database
A Database Link (DB Link) is a direct connection between two databases that allows you to execute SQL statements on a remote database from your APEX instance.
Steps to Create and Use a Database Link
Create a Database Link in SQL Workshop (SQL Commands or SQL Scripts).
CREATE DATABASE LINK target_db_link
CONNECT TO remote_user IDENTIFIED BY 'password'
USING 'target_db_service_name';
Replace target_db_link with the name of the database link, remote_user with the username of the target database, password with its password, and target_db_service_name with the correct connection string.
Query Data from the Remote Database Using SQL
SELECT * FROM employees@target_db_link;
This retrieves employee data from the target database.
Use the Database Link in PL/SQL Procedures
BEGIN
FOR rec IN (SELECT first_name, last_name FROM employees@target_db_link) LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
This PL/SQL block retrieves employee names from the target database and prints them.
Inserting Data into the Target Database
INSERT INTO employees@target_db_link (id, first_name, last_name)
VALUES (101, 'John', 'Doe');
This inserts a new employee record into the remote database.
Updating Data in the Target Database
UPDATE employees@target_db_link
SET first_name = 'Jane'
WHERE id = 101;
This updates the first name of the employee with ID 101.
Deleting Data from the Target Database
DELETE FROM employees@target_db_link WHERE id = 101;
This removes an employee record from the remote database.
Using RESTful Web Services to Access a Target Database
When direct database links are not available or secure, RESTful Web Services (ORDS) can be used to fetch data from the target database through HTTP requests.
Steps to Access a Target Database Using RESTful Web Services
Enable Oracle REST Data Services (ORDS) on the Target Database
This allows you to expose data as RESTful endpoints.Create a RESTful Web Service to Retrieve Data
Example: Exposing employee data from the target database as JSON.
BEGIN
ORDS.define_service(
p_module_name => 'employees_api',
p_base_path => 'employees/',
p_pattern => 'all',
p_source_type => ORDS.source_type_query,
p_source => 'SELECT * FROM employees',
p_method => 'GET'
);
END;
This creates a REST API endpoint that returns employee data when accessed via HTTP.
Consume the RESTful API from Oracle APEX
Navigate to Shared Components > Web Source Modules.
Create a new Web Source using the REST API URL (https://api.targetdatabase.com/employees).
Use the Web Source Module in Interactive Reports, Forms, or Charts.
Query the Web Service Using SQL
SELECT * FROM apex_web_service.make_rest_request(
p_url => 'https://api.targetdatabase.com/employees',
p_http_method => 'GET'
);
This retrieves JSON data from the external database.
Using Oracle Autonomous Database Connectivity
If your APEX application is running on Oracle Autonomous Database, you can leverage Oracle-managed heterogeneous connectivity to access external databases securely.
Steps to Use Oracle Autonomous Database to Access a Target Database
Use the DBMS_CLOUD Package to Query Remote Data
SELECT * FROM DBMS_CLOUD.list_objects(
'my_credential',
'https://my-target-database.com/employees'
);
This retrieves data directly from an external database or cloud storage.
Use a Secure Wallet to Connect to the Target Database
Download the Wallet File from the target database.
Upload it to APEX Instance Settings under Database Connectivity.
Configure APEX to use the wallet for secure authentication.
Create a Credential to Authenticate the Target Database
BEGIN
DBMS_CLOUD.create_credential(
credential_name => 'my_credential',
username => 'remote_user',
password => 'password'
);
END;
This securely stores the username and password for the external database.
Query Data from the Target Database Using the Credential
SELECT * FROM employees@my_target_db USING my_credential;
This securely retrieves employee data from the target database.
Best Practices for Secure Database Connectivity
Always use SSL/TLS encryption when connecting to a target database.
Avoid storing credentials in queries—use APEX credentials instead.
If using RESTful Web Services, cache frequently accessed data to improve performance.
Monitor database link usage to ensure performance efficiency.
Restrict database link access to only authorized users.
