Connecting your APEX apps to Non-Oracle Databases with Oracle Autonomous Databases

This blog post was originally published on 9 September, 2022.

Many customers use different databases including Oracle and they face a common challenge to integrate information across these different data sources. Thus, a frequent question is how do they can access data from their Non-Oracle Databases, such as Microsoft SQL Server, MySQL, etc., with their APEX app.

One easy way to do it is through REST Services that you can easily consume in your APEX apps. Learn more about Creating a REST Data Source.
Another way probably less known is creating database links to those Non-Oracle Databases with Oracle-Managed Heterogeneous Connectivity.

In this blog post, you will learn how to access and maintain data from Microsoft SQL Server Database in your APEX apps. You will start creating database links from an Autonomous Database to an Oracle Database Gateway to access Non-Oracle databases, then create a view and a package to access and maintain the data and finally build an APEX app to put everything together.

An Oracle Database Gateway is a gateway that is designed for accessing a specific non-Oracle system. Using an Oracle Database Gateway, you can access data anywhere in a distributed database system without knowing either the location of the data or how it is stored.

Benefits

Transparent and direct to access data in non-Oracle databases from an Oracle environment, reducing data duplication.
Eliminates the need to customize your applications to access data from non-Oracle databases.
Query several different databases at once, allowing you to consolidate the data in just one place.
Heterogeneous Services has a passthrough feature that enables you to bypass Oracle’s query processor and to communicate with the remote database in its own language.

Prerequisites

The target database must be accessible from the public internet on the port number supported for the specified database type. See Oracle-Managed Heterogeneous Connectivity Database Types and Ports for the list of supported non-Oracle database types and ports.
The target database must be configured to allow incoming SSL/TLS connections.

Connect to your Autonomous Database

Sign in your Oracle Cloud instance and provision an Autonomous Database following the steps described here: Autonomous Database in Oracle Cloud
In case you don’t have an Oracle Cloud instance yet, sign up for a free account on oracle.com/free and take advantage of the Always Free Services.
Once your Autonomous Database is available, click Database Actions. Enter the password you used to create your Autonomous Database.
Click Database Users to create a new user that will access to your target database.
Click Create User and enter the following details:
User Name: AZURE
Password: <your_password>
Confirm Password: <your_password>
Enable the Web Access option
In the Granted Roles tab, make sure that user has checked the following roles: connect and resource
Click Create User.
Copy the link to access Database Actions with this new user.

Assign Privileges

At the top left, click the hamburger menu.
Click SQL.
Run the following script:

GRANT EXECUTE ON DBMS_CLOUD TO AZURE;
GRANT EXECUTE ON DBMS_CLOUD_ADMIN TO AZURE;
GRANT CREATE DATABASE LINK TO AZURE;
GRANT READ,WRITE ON DIRECTORY DATA_PUMP_DIR TO AZURE;
GRANT CREATE VIEW TO AZURE;

Create Credentials

On Autonomous Database create credentials to access the target database, in this case a Microsoft SQL Server database. The username and password you specify with DBMS_CLOUD.CREATE_CREDENTIAL are the credentials for the target database used within the database link.

Go to the link you got in the previous steps to access Database Actions, enter your credentials and click Sign in.
Username: AZURE
Password: <your_password>

Click on SQL and run the the following command:

    BEGIN
	DBMS_CLOUD.CREATE_CREDENTIAL(
	    credential_name => 'AZURE',
	    username => 'azureuser',
	    password => <your_password>
    
	  );
	END;
	/

Create the database link to the target database using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'AZURE_DBLINK', 
          hostname => 'mgm.database.windows.net', 
          port => '1433',
          service_name => 'mySampleDatabase',
          credential_name => 'AZURE',
          gateway_params => JSON_OBJECT('db_type'  value 'AZURE'),
          ssl_server_cert_dn => NULL);
END;
/

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 configures and handles the secure connection to a target database and your connections are end-to-end encrypted. Oracle-managed heterogeneous connectivity is preconfigured with a wallet that contains most of the common trusted root and intermediate SSL certificates. Thus, NULL must be provided as the value for the ssl_server_cert_dn parameter.
To ensure security when using database links with Oracle-managed heterogeneous connectivity, the connection port is restricted and must have SSL/TLS enabled. You specify the target database port with the port parameter.

Access data on the target database

Run the following command to fetch the number of rows in the selected table:
```
	
SELECT count(*) FROM sales.customers@AZURE_DBLINK;
```
Create a view to easily access the data

CREATE VIEW customers_view (customer_id, name, phone, email, street, city, state_, zip_code) AS
select "customer_id",
       "first_name" || ' '|| "last_name" name,
       "phone",
       "email",
       "street",
       "city",
       "state",
       "zip_code"
  from sales.customers@AZURE_DBLINK;

Access the target database with your APEX App

Go back to the Autonomous database homepage and click Tools.
Click Open APEX.
Sign in the Administration Services with your credentials.
Password: Enter the password you used to create your Autonomous Database.
Click Create Workspace from an exsiting schema

Image 1. Creating APEX workspaces
Enter the following and click Create Workspace:
Database user – select AZURE
Workspace Name – enter AZURE
Workspace Username – enter AZURE
Workspace Password – enter <your_password>
At the top you will see the message “Workspace created. Sign out of Administration Services and sign in to AZURE to begin building applications”. Click on the workspace name.

Image 2. Workspace created
Enter your credentials and click Sign in.

Watch the video above to learn how to access and maintain the data from your APEX app.

Package to manage the customers table

CREATE OR replace PACKAGE maintain_customers
AS
  PROCEDURE insert_customer (
    p_first_name IN VARCHAR2,
    p_last_name  IN VARCHAR2,
    p_phone      IN VARCHAR2,
    p_email      IN VARCHAR2,
    p_street     IN VARCHAR2,
    p_city       IN VARCHAR2,
    p_state      IN VARCHAR2,
    p_zip_code   IN VARCHAR2);
  PROCEDURE update_customer (
    p_customer_id IN NUMBER,
    p_first_name  IN VARCHAR2,
    p_last_name   IN VARCHAR2,
    p_phone       IN VARCHAR2,
    p_email       IN VARCHAR2,
    p_street      IN VARCHAR2,
    p_city        IN VARCHAR2,
    p_state       IN VARCHAR2,
    p_zip_code    IN VARCHAR2);
  PROCEDURE delete_customer (
    p_customer_id IN NUMBER);
END maintain_customers; 
/
CREATE OR replace PACKAGE BODY maintain_customers
AS
  PROCEDURE Insert_customer (p_first_name IN VARCHAR2,
                             p_last_name  IN VARCHAR2,
                             p_phone      IN VARCHAR2,
                             p_email      IN VARCHAR2,
                             p_street     IN VARCHAR2,
                             p_city       IN VARCHAR2,
                             p_state      IN VARCHAR2,
                             p_zip_code   IN VARCHAR2)
  IS
  BEGIN
      INSERT INTO sales.customers@azure_dblink
                  ("first_name",
                   "last_name",
                   "phone",
                   "email",
                   "street",
                   "city",
                   "state",
                   "zip_code")
      VALUES      (p_first_name,
                   p_last_name,
                   p_phone,
                   p_email,
                   p_street,
                   p_city,
                   p_state,
                   p_zip_code);
  END insert_customer;
  PROCEDURE Update_customer (p_customer_id IN NUMBER,
                             p_first_name  IN VARCHAR2,
                             p_last_name   IN VARCHAR2,
                             p_phone       IN VARCHAR2,
                             p_email       IN VARCHAR2,
                             p_street      IN VARCHAR2,
                             p_city        IN VARCHAR2,
                             p_state       IN VARCHAR2,
                             p_zip_code    IN VARCHAR2)
  IS
  BEGIN
      UPDATE sales.customers@azure_dblink
      SET    "first_name" = p_first_name,
             "last_name" = p_last_name,
             "phone" = p_phone,
             "email" = p_email,
             "street" = p_street,
             "city" = p_city,
             "state" = p_state,
             "zip_code" = p_zip_code
      WHERE  "customer_id" = p_customer_id;
  END update_customer;
  PROCEDURE Delete_customer (p_customer_id IN NUMBER)
  IS
  BEGIN
      DELETE FROM sales.customers@azure_dblink
      WHERE  "customer_id" = p_customer_id;
  END delete_customer;
END maintain_customers; 
/

Performance Recommendations

Optimize your SQL statements following the same guidelines as you were accessing Oracle data only. Take in mind that the non-Oracle databases usually don’t support all the functions and operators that Oracle supports.
Using local views can improve the performance of your queries. Besides, if your data doesn’t change frequently, you can create materialized views in your Oracle database to speed up the app’s performance.

Conclusion

Oracle APEX allows you to display, manipulate, chart, and process data as easily and efficiently as possible regardless where your data comes from, whether it is from a local database, remote database, non-oracle database or a web service.

Oracle APEX features state-of-the-art functionality to help you turn data into information!

Connecting your APEX apps to Non-Oracle Databases with Oracle Autonomous Databases

Benefits

Prerequisites

Connect to your Autonomous Database

Assign Privileges

Create Credentials

Access data on the target database

Access the target database with your APEX App

Package to manage the customers table

Performance Recommendations

Conclusion

Monica Godoy

Senior Principal Product Manager

Getting started with APEX on Oracle Cloud - QuickStart

APEX 19.2 - Faceted Search

Connecting your APEX apps to Non-Oracle Databases with Oracle Autonomous Databases

Benefits

Prerequisites

Connect to your Autonomous Database

Assign Privileges

Create Credentials

Access data on the target database

Access the target database with your APEX App

Package to manage the customers table

Performance Recommendations

Conclusion

Authors

Monica Godoy

Senior Principal Product Manager

Getting started with APEX on Oracle Cloud - QuickStart

APEX 19.2 - Faceted Search