How Do I Access an External Databases Using SQL in Oracle APEX

 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

1. Connect to the Oracle Database using SQL Developer or SQL*Plus.

2. 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

1. Go to SQL Workshop → RESTful Services.

2. Click Create to define a new RESTful service.

3. Enter the Base URL of the external database API.

4. Define HTTP methods (GET, POST, PUT, DELETE).

5. 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

1. Install Oracle REST Data Services (ORDS) on the Oracle server.

2. Define a RESTful Web Service to query external database tables.

3. Register the ORDS API in APEX as a Web Source Module.

4. 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

1. Install Oracle Database Gateway on the Oracle Database server.

2. Create a Database Link that connects to the external database.

3. 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

1. Upload a CSV file to the Oracle Directory.

2. 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')

);

3. 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.

Table 1 - Database values
db_type Value	Database Type	Required Port
AWSREDSHIFT	Amazon Redshift	5439
AZURE	Microsoft SQL Server SQL Synapse Analytics	1433
MYSQL	MySQL	3306
POSTGRES	PostgreSQL	5432
SNOWFLAKE	Snowflake	443

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.

How Do I Delete a Row Using SQL in Oracle APEX

Deleting a row from a database table in Oracle APEX can be done in multiple ways, depending on how the user interacts with the application. This tutorial covers different methods to delete a row using SQL, including SQL commands, PL/SQL processes, dynamic actions, and interactive grid reports.

1. Deleting a Row Using a SQL DELETE Statement

The simplest way to delete a row is to use a SQL DELETE statement.

Basic SQL DELETE Syntax

DELETE FROM employees WHERE emp_id = 101;

This statement removes the row where emp_id is 101 from the employees table.

2. Creating a DELETE Process in APEX Using a Page Item

In a form-based APEX application, a user selects a record, and a button triggers the deletion process.

Steps to Create a Delete Process in APEX

1. Create a Form Page based on the table you want to modify.

2. Ensure there is a page item (e.g., P1_EMP_ID) to store the row ID.

3. Add a Delete button.

4. Go to Processing and click Create Process.

5. Choose PL/SQL Process and enter the following code:

BEGIN

    DELETE FROM employees WHERE emp_id = :P1_EMP_ID;

    COMMIT;

END;

6. Set Condition to When Button Pressed → Select the Delete button.

7. Save and run the page.

Now, when a user selects a record and clicks the Delete button, the row is removed from the table.

3. Deleting a Row from an Interactive Grid

Interactive Grids allow users to manage records, including deleting rows directly.

Steps to Enable Deletion in an Interactive Grid

1. Create an Interactive Grid based on a table.

2. Select the grid region and go to Attributes.

3. In the Edit section, enable Allow Row Deletion.

4. A delete icon appears in the grid. Users can select rows and delete them.

5. Ensure that Automatic Row Processing (DML) is enabled under Processing.

Now users can delete rows directly from the Interactive Grid.

4. Using a Dynamic Action to Delete a Row Without Reloading the Page

Dynamic Actions allow deleting records without a full-page refresh.

Steps to Create a Dynamic Action for Deletion

1. Create a Delete Button on the page.

2. Go to Dynamic Actions and click Create Dynamic Action.

3. Set Event to Click and Selection Type to Button.

4. Add a True Action → Execute PL/SQL Code and enter:

BEGIN

    DELETE FROM employees WHERE emp_id = :P1_EMP_ID;

    COMMIT;

END;

5. Under Settings, set Items to Submit → P1_EMP_ID.

6. Add another True Action → Refresh → Select the report region.

7. Save and test.

When the Delete button is clicked, the row is removed, and the report refreshes automatically.

5. Deleting a Row Using SQL and APEX_COLLECTIONS

APEX Collections allow temporary data storage and batch deletions.

Steps to Delete Using APEX Collections

1. Store Data in an APEX Collection

BEGIN

    APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION('EMP_DELETE');

    APEX_COLLECTION.ADD_MEMBER(p_collection_name => 'EMP_DELETE', p_c001 => :P1_EMP_ID);

END;

2. Process the Deletion

BEGIN

    FOR rec IN (SELECT c001 emp_id FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'EMP_DELETE')  

    LOOP

        DELETE FROM employees WHERE emp_id = rec.emp_id;

    END LOOP;

    COMMIT;

END;

This approach is useful for managing deletions across multiple sessions before finalizing the database update.

6. Using a Confirmation Message Before Deleting

To prevent accidental deletions, a confirmation message can be added before executing the delete action.

Steps to Add Confirmation to a Delete Button

1. Select the Delete button in APEX.

2. Go to Behavior and find Confirmation.

3. Enter a message such as:

"Are you sure you want to delete this record?"

4. Save and test.

Now, when a user clicks Delete, a pop-up asks for confirmation before executing the delete process.

7. Best Practices for Deleting Rows in APEX

• Always use a confirmation message before deleting records.

• Use bind variables (:P1_EMP_ID) instead of replacement strings (&P1_EMP_ID.) to prevent SQL injection.

• Ensure proper user authorization before allowing delete actions.

• Consider using soft deletes by marking records as inactive instead of permanently deleting them.

• Always test in a development environment before deploying changes.

Oracle APEX provides multiple ways to delete a row using SQL, including form-based deletion, dynamic actions, interactive grids, and APEX collections. Each method has its advantages depending on the application requirements. By implementing best practices like using confirmation messages, securing inputs, and avoiding accidental deletions, you can ensure a smooth and user-friendly experience.