Oracle APEX allows you to connect to external databases and query them using SQL. This can be useful when working with data sources that are not directly stored in your Oracle Database. There are several ways to achieve this, including Database Links, RESTful Web Services, ORDS (Oracle REST Data Services), and Oracle Database Gateway.
1. Using a Database Link to Access an External Database
A database link allows an Oracle database to query tables and views from another Oracle database or even a non-Oracle database.
Creating a Database Link
Connect to the Oracle Database using SQL Developer or SQL*Plus.
Run the following SQL command to create a database link:
CREATE DATABASE LINK remote_db
CONNECT TO remote_user IDENTIFIED BY remote_password
USING 'remote_service_name';
remote_db: The name of the database link.
remote_user: The username on the remote database.
remote_password: The password for authentication.
remote_service_name: The connection string for the remote database.
Querying an External Table via Database Link
Once the database link is created, you can query the external database using the @ symbol.
SELECT * FROM employees@remote_db;
To join local and external tables, use:
SELECT a.emp_id, a.emp_name, b.department_name
FROM local_employees a
JOIN departments@remote_db b ON a.dept_id = b.dept_id;
Updating Data in an External Database
UPDATE employees@remote_db
SET salary = salary * 1.1
WHERE department_id = 10;
Deleting Data in an External Database
DELETE FROM employees@remote_db WHERE emp_id = 100;
2. Accessing External Databases Using RESTful Web Services
Oracle APEX can consume external database data using RESTful Web Services. This is useful for accessing cloud-based databases or third-party systems.
Steps to Configure a RESTful Web Service in APEX
Go to SQL Workshop → RESTful Services.
Click Create to define a new RESTful service.
Enter the Base URL of the external database API.
Define HTTP methods (GET, POST, PUT, DELETE).
Test the API in Postman or using curl to verify the response.
Consuming REST API Data in APEX via SQL Query
Use the apex_web_service.make_rest_request function to fetch data.
SELECT apex_web_service.make_rest_request(
p_url => 'https://api.example.com/employees',
p_http_method => 'GET'
) FROM dual;
To insert API data into a local table:
BEGIN
INSERT INTO employees (emp_id, emp_name, department)
VALUES (
JSON_VALUE(apex_web_service.make_rest_request(
p_url => 'https://api.example.com/employee/101',
p_http_method => 'GET'
), '$.id'),
JSON_VALUE(apex_web_service.make_rest_request(
p_url => 'https://api.example.com/employee/101',
p_http_method => 'GET'
), '$.name'),
JSON_VALUE(apex_web_service.make_rest_request(
p_url => 'https://api.example.com/employee/101',
p_http_method => 'GET'
), '$.department')
);
COMMIT;
END;
3. Using Oracle REST Data Services (ORDS) to Access External Databases
ORDS allows databases to expose SQL queries as RESTful APIs.
Steps to Configure ORDS for an External Database
Install Oracle REST Data Services (ORDS) on the Oracle server.
Define a RESTful Web Service to query external database tables.
Register the ORDS API in APEX as a Web Source Module.
Query external data using APEX SQL queries.
Example: Querying an external ORDS endpoint in APEX:
SELECT * FROM apex_rest_web_services WHERE service_name = 'external_db_api';
4. Accessing External Data via Oracle Database Gateway
Oracle Database Gateway enables SQL access to external databases like SQL Server, MySQL, and PostgreSQL.
Steps to Configure Oracle Database Gateway
Install Oracle Database Gateway on the Oracle Database server.
Create a Database Link that connects to the external database.
Query the external database using standard SQL commands.
Example: Querying an external SQL Server database:
SELECT * FROM employees@sqlserver_link;
5. Using External Tables to Read External Data Files
If the external database provides CSV, JSON, or XML files, use External Tables to read them in APEX.
Steps to Create an External Table for CSV Data
Upload a CSV file to the Oracle Directory.
Create an external table pointing to the file.
CREATE TABLE employees_ext (
emp_id NUMBER,
emp_name VARCHAR2(100),
department VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY my_directory
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
)
LOCATION ('employees.csv')
);
Query the external table like a normal table.
SELECT * FROM employees_ext;
6. Best Practices When Accessing External Databases
Use database links for Oracle-to-Oracle connections.
Use RESTful Web Services for cloud-based or third-party databases.
Use ORDS for exposing SQL data as REST APIs.
Use external tables for structured data files.
Ensure proper authentication and security when accessing external systems.
Use APEX error handling to handle failed connections gracefully.
Oracle APEX provides multiple ways to access external databases using SQL. Whether through database links, RESTful web services, ORDS, database gateways, or external tables, APEX makes it easy to integrate and query external data sources. The right approach depends on factors like database type, security requirements, and performance considerations.
The service_name is the database name of the non-Oracle database and the gateway_params db_type value that you supply must be one of the supported values. See the database values and port in the table below.
Autonomous Database automatically sets up and manages secure connections to the target database, ensuring that all connections are encrypted end-to-end. Oracle-managed heterogeneous connectivity comes preconfigured with a wallet that includes most commonly trusted root and intermediate SSL certificates. As a result, the ssl_server_cert_dn parameter should be set to NULL.
For security purposes, when using database links with Oracle-managed heterogeneous connectivity, connections are restricted to a specific port, which must have SSL/TLS enabled. The target database port is specified using the port parameter.
No comments:
Post a Comment