One of the big announcements this month was the Oracle Database Service for Microsoft Azure. With this new fully managed service, Azure customers can seamlessly build Azure applications using the high-performance, high-availability, and automated management of Oracle Database services such as Autonomous Database running on OCI. Did you know that even before this announcement you could have a REST interface to Azure SQL through ORDS?
In this article I’ll show you how I have data from a hosted Azure SQL database used in an APEX application all made possible through ORDS. Although I’m using released products it is important to point out that using ORDS with SQL Server is not officially supported. Therefore, although you can use this approach, you mind find a use case that does not work. For example, an unsupported column data type.
Key components in this article
- ORDS 22.2.0 – running locally in standalone mode
- APEX 22.1 – installed in local Oracle 19c database
- Oracle 19c database – running locally in docker
- Microsoft SQL Azure (RTM) – 12.0.2000.8 – running in Azure
- Azure Data Studio Version: 1.38.0 – running locally
- mssql-jdbc-10.2.0.jre11.jar – in the ORDS lib/ext/ folder
Azure SQL Sample Database
To get started, if you do not have an Azure SQL database already, head over to https://portal.azure.com/ and set yours up. By default you will get a functional, but empty database. In my case, I selected Sample for Use existing data option in the Additional Settings section of the create database sequence so that I had some data in place.
The overall process from zero involves creating an SQL Server ( pobdemo in my case ) and an SQL Database ( pobsample in my case ) which can be accessed outside of Azure.
You will also need to create some logins and users to access the database so Azure Data Studio, or some other client for working with Azure SQL will be needed. The administrator user should not be used for anything more than managing the database.
One login will be used by ORDS to verify the pool connection configuration. It does not require any specific privileges in the database just the ability to make a connection over JDBC. The login could be any value but for consistency we’ll call it ORDS_PUBLIC_USER
CREATE LOGIN ORDS_PUBLIC_USER WITH PASSWORD = '< keep this secret >';
The other login and database user is for the account that will be used to interact with the database. In this example: ords_demo
CREATE LOGIN ords_demo WITH PASSWORD = '< keep this secret >';
CREATE USER ords_demo FROM LOGIN ords_demo;
ALTER ROLE db_datareader ADD MEMBER [ords_demo];
ALTER ROLE db_datawriter ADD MEMBER [ords_demo];
Configure ORDS
Now that we have our database accounts let’s configure ORDS. In my case I already have an ORDS instance setup with the default pool configured for my Oracle 19c database and APEX is installed.
The directory that I extracted the ORDS 22.2.0 distribution to is ~/Downloads/ords-22.2.0.172.1758/ and the configuration directory is ~/Documents/Personal/azure_sql/. That directory looks like this:
├──databases │ ├── azure_sql │ │ ├── pool.xml │ │ └── wallet │ │ └── cwallet.sso │ └── default │ ├── pool.xml │ └── wallet │ └── cwallet.sso ├── global │ └── settings.xml
The configuration for the azure_sql pool was achieved as follows while the configuration directory is the working directory. Note that you will need to know your JDBC connection string from your Azure Dashboard. You’ll find that under Show database connection strings.
Armed with that information it is time to configure your azure_sql pool:
~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql set db.connectionType customurl ~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql set db.customURL jdbc:sqlserver://<rest of the JDBC connection string from Azure dashboard> ~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql set jdbc.driverName com.microsoft.sqlserver.jdbc.SQLServerDriver ~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql set db.credentialsSource request ~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql set restEnabledSql.active true ~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql set db.username ORDS_PUBLIC_USER
Every command entry should show a message confirming that the setting was applied. Now to set the password for ORDS_PUBLIC_USER. That should be treated as a secret:
~/Downloads/ords-22.2.0.172.1758/bin/ords config --db-pool azure_sql secret db.password Enter the database password: Confirm password: The setting named: db.password was set to: ****** in configuration: azure_sql
When done, assuming that you are using the same pool name, your databases/azure_sql/pool.xml should look like this but without the comments:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Fri Oct 11 11:21:28 IST 2019</comment>
<entry key="db.connectionType">customurl</entry>
<entry key="db.customURL">jdbc:sqlserver://your_sql_server.database.windows.net:1433;database=your_sql_db;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;</entry>
<entry key="jdbc.driverName">com.microsoft.sqlserver.jdbc.SQLServerDriver</entry>
<!-- username for account required to verify pool connection details are
correct. User does not require special privileges -->
<entry key="db.username">ORDS_PUBLIC_USER</entry>
<!-- Credentials in the request will be used to create injected DB connection
and ORDS Role will be SQL Developer. Authentication mode will be ANY_SCHEMA -->
<entry key="db.credentialsSource">request</entry>
<!-- REST Enabled SQL must be enabled -->
<entry key="restEnabledSql.active">true</entry>
</properties>One of the settings is the JDBC driver to use ( jdbc.driverName ) and that is com.microsoft.sqlserver.jdbc.SQLServerDriver but ORDS does not ship with that JDBC driver. Download the SQL Server JDBC driver and copy the Java 11 jar into the ORDS lib/ext directory. In my case that is ~/Downloads/ords-22.2.0.172.1758/lib/ext
Time to startup ORDS in standalone mode and verify the connection pool is loaded correctly.
~/Downloads/ords-22.2.0.172.1758/bin/ords serve ORDS: Release 22.2 Production on Fri Jul 29 13:52:06 2022 Copyright (c) 2010, 2022, Oracle. ... 2022-07-29T13:52:10.561Z INFO Configuration properties for: |azure_sql|lo| gopherProxySet=false awt.toolkit=sun.lwawt.macosx.LWCToolkit java.specification.version=11 ... 2022-07-29T13:52:45.126Z INFO Oracle REST Data Services initialized Oracle REST Data Services version : 22.2.0.r1721758 Oracle REST Data Services server info: jetty/9.4.46.v20220331 Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 11.0.13+10-LTS-370
Test REST Enabled SQL
If you have gotten this far without errors that means ORDS can connect to the Azure SQL database using the db.username account and therefore the custom connection aspect of the pool configuration is correct. Now to confirm the REST Enabled SQL can use that pool configuration to access the database with basic authentication credentials provided. For that we’ll use the login that is a user in the database and cURL to submit a GET request
curl -u ords_demo http://localhost:8080/ords/azure_sql/_/sql
Enter host password for user 'ords_demo': ********
{
"database_product_name":"Microsoft SQL Server",
"database_product_version":"12.00.312",
"database_major_version":12,
"database_minor_version":0,
"env":{"defaultTimeZone":"UTC","ordsVersion":"22.2.0.r1721758"}
}
We could just stop here and just use REST Enabled SQL directly with our Azure SQL database but let’s do something really interesting with APEX, which is probably why you’re here. First, a few words about how APEX works with SQL Server natively: it does not.
SQL Server Syntax
APEX has powerful support for consuming data over REST services including the ability to construct queries for Oracle and MySQL which are executed through ORDS REST Enabled SQL. This allows for a very dynamic no code approach to developing applications. See previous article How to use the Oracle Database Tools Service to provide MySQL data to APEX on this topic.
APEX does not provide the same level of support for SQL Server. Although one can configure an ORDS pool so that REST Enabled SQL can be used, APEX will not recognise the database type when you try create the REST Enabled SQL reference. All is not lost though. The work around is to write ORDS REST Services which have the specific SQL Server statements. Those services make the REST Enabled SQL calls and return the response. They are effectively custom wrapper services.
Now, on with the APEX adventure…
APEX Workspace
In my local Oracle database I have APEX installed and have created a workspace called AZURE which is using the AZURE database schema. That schema has been REST Enabled too.
This AZURE database schema in the Oracle database will have the REST Services ( module/template/handler ) defined and those services will make the REST Enabled SQL calls. For convenience these services are not secured but obviously should be if they are to be used in a real world scenario. What is secured is the REST Enabled SQL endpoint. A username and password for the database user in Azure SQL must be provided but rather than having that in plain text in the handler source we’ll use an APEX Web Credential that the AZURE schema will have access to.
APEX Web Credentials
Web Credentials are a really useful feature in APEX that allows you to store authentication credentials for external REST services or REST Enabled SQL services. We’ll use that in our ORDS Service handler pl/sql block but more on that later. First, let’s define a Web Credential in the workspace.
There are a few things to highlight about the above Credentials for Azure
- The Static Identifier is Credentials_for_Azure – we will refer to that later in the ORDS handler
- The Authentication Type is Basic Authentication – the REST Enabled SQL endpoint uses this Authentication Type
- The Valid for URLs specifies host.docker.internal as the server address. Remember how I said that Oracle 19c database was running in docker? Localhost would just point to the running container in my case. This is how the database can address the host machine ( my laptop ) which is running ORDS.
Stating the obvious here but it’s significant that you can change the username and password in the Web Credential without modifying the handler. Just thought I’d mention that.
And now with the Web Credential in place lets define the services in the AZURE schema that will submit SQL queries over REST Enabled SQL to Azure SQL database.
Wrapper Services
We’re going to define two services which follow this pattern: send a query, return the response. This relies on the APEX_WEB_SERVICE.MAKE_REST_REQUEST function to create the POST request, with Content-Type application/sql, and return the payload response. The previously defined Web Credentials are used to authenticate those requests.
The module, templates and handlers can be defined in APEX or SQL Developer Web. One module with Base Path /sales/ along with two templates: customer_count_by_country and order_header.
Handler for /sales/customer_count_by_country
This handler executes an aggregation query to get the number of customers with a Main Office in each country.
DECLARE
l_clob CLOB;
l_result VARCHAR2(32767);
BEGIN
APEX_WEB_SERVICE.g_request_headers.delete();
APEX_WEB_SERVICE.g_request_headers(1).name := 'Content-Type';
APEX_WEB_SERVICE.g_request_headers(1).value := 'application/sql';
l_clob := APEX_WEB_SERVICE.make_rest_request(
p_url => 'http://host.docker.internal:8080/ords/azure_sql/_/sql',
p_http_method => 'POST',
p_credential_static_id => 'Credentials_for_Azure',
p_body => 'select address.CountryRegion, count(CustomerAddress.CustomerID) as "CustomerCount"
from SalesLT.Address, SalesLt.CustomerAddress
where CustomerAddress.AddressID = Address.AddressID and CustomerAddress.AddressType = ''Main Office''
group by address.CountryRegion'
);
HTP.print(l_clob);
END;Handler for /sales/order_header
This handler executes a basic query to get all records from the SalesOrderHeader table.
DECLARE
l_clob CLOB;
l_result VARCHAR2(32767);
BEGIN
APEX_WEB_SERVICE.g_request_headers.delete();
APEX_WEB_SERVICE.g_request_headers(1).name := 'Content-Type';
APEX_WEB_SERVICE.g_request_headers(1).value := 'application/sql';
-- Get the XML response from the web service.
l_clob := APEX_WEB_SERVICE.make_rest_request(
p_url => 'http://host.docker.internal:8080/ords/azure_sql/_/sql',
p_http_method => 'POST',
p_credential_static_id => 'Credentials_for_Azure',
p_body => 'SELECT * FROM [SalesLT].[SalesOrderHeader]'
);
HTP.print(l_clob);
END;Service Handler Walkthrough
Keep in mind that these handlers execute in the AZURE database schema which is the schema for the APEX Workspace we’ll create the Data Source references and APEX Application in. Both handlers follow these steps:
- Set the Content-Type header to application/sql
- Make a POST request with Web Credentials for authentication and a query
- Write the REST Enabled SQL result to the HTTP Response stream.
There’s no validation on the response, or parsing that response to change the structure. We’ll leave it to APEX to figure out how to parse the response payload.
Now is it a good time to verify that the two handlers work. We can invoke them through cURL. Remember the schema alias is azure, the module base path is /sales/ so the URL will begin with http://localhost:8080/ords/azure/sales/
curl http://localhost:8080/ords/azure/sales/customer_count_by_country
{
"env" : {
"defaultTimeZone" : "UTC"
},
"items" : [
{
"response" : [],
"result" : 0,
"resultSet" : {
"count" : 3,
"hasMore" : false,
"items" : [
{
"countryregion" : "Canada",
"customercount" : 106
},
{
"countryregion" : "United Kingdom",
"customercount" : 38
},
{
"countryregion" : "United States",
"customercount" : 263
}
],
"limit" : 10000,
"metadata" : [
{
"columnClassName" : "java.lang.String",
"columnName" : "CountryRegion",
"columnTypeName" : "nvarchar",
"isNullable" : 0,
"jsonColumnName" : "countryregion",
"precision" : 50,
"scale" : 0
},
{
"columnClassName" : "java.lang.Integer",
"columnName" : "CustomerCount",
"columnTypeName" : "int",
"isNullable" : 1,
"jsonColumnName" : "customercount",
"precision" : 10,
"scale" : 0
}
],
"offset" : 0
},
"statementId" : 1,
"statementPos" : {
"endLine" : 5,
"startLine" : 1
},
"statementText" : "select address.CountryRegion, count(CustomerAddress.CustomerID) as \"CustomerCount\"\nfrom SalesLT.Address, SalesLt.CustomerAddress\nwhere CustomerAddress.AddressID = Address.AddressID and CustomerAddress.AddressType = 'Main Office'\ngroup by address.CountryRegion",
"statementType" : "query"
}
]
}
curl http://localhost:8080/ords/azure/sales/order_header
{
"env" : {
"defaultTimeZone" : "UTC"
},
"items" : [
{
"response" : [],
"result" : 0,
"resultSet" : {
"count" : 32,
"hasMore" : false,
"items" : [
{
"accountnumber" : "10-4020-000609",
"billtoaddressid" : 1092,
"comment" : null,
"creditcardapprovalcode" : null,
"customerid" : 29847,
"duedate" : "2008-06-13T00:00:00Z",
"freight" : 22.0087,
"modifieddate" : "2008-06-08T00:00:00Z",
"onlineorderflag" : false,
"orderdate" : "2008-06-01T00:00:00Z",
"purchaseordernumber" : "PO348186287",
"revisionnumber" : 2,
"rowguid" : "89E42CDC-8506-48A2-B89B-EB3E64E3554E",
"salesorderid" : 71774,
"salesordernumber" : "SO71774",
"shipdate" : "2008-06-08T00:00:00Z",
"shipmethod" : "CARGO TRANSPORT 5",
"shiptoaddressid" : 1092,
"status" : 5,
"subtotal" : 880.3484,
"taxamt" : 70.4279,
"totaldue" : 972.785
},
...trimmed for brevity...
],
"limit" : 10000,
"metadata" : [
{
"columnClassName" : "java.lang.Integer",
"columnName" : "SalesOrderID",
"columnTypeName" : "int",
"isNullable" : 0,
"jsonColumnName" : "salesorderid",
"precision" : 10,
"scale" : 0
},
...trimmed for more brevity...
],
"offset" : 0
},
"statementId" : 1,
"statementPos" : {
"endLine" : 2,
"startLine" : 1
},
"statementText" : "SELECT * FROM [SalesLT].[SalesOrderHeader]",
"statementType" : "query"
}
]
}
Now is a good time to reiterate that the ORDS REST Services are not protected and if the Azure SQL database you’re connecting to had any sensitive data it should be protected but that’s not the focus of this article.
As you can see from the above tests, these are REST Enabled SQL responses with lots of information about the statement executed, the metadata of the columns when all we’re really interested in is the data at items.resultSet.items. That will be relevant when we look into Data Sources.
APEX Data Sources
Let’s create a basic APEX application called Azure SQL with just the defaults.
Once created, edit the application and navigate to the Shared Components section.
The Data Source references section has various options for getting data over REST.
Define REST Data Sources for both endpoints but specify their REST Data Source Type as Simple HTTP.
Remember how the REST Enabled SQL response structure contains lots of metadata? For Data Profile specify the Row Selector as items.resultSet.items so that APEX can find the array of rows that it can interrogate for Data Profile.
Two Data Source references must be defined. In my case I also defined synchronisation for the AzureSalesHeader Data Source to pull in data to a local table. I’ll cover that in a separate article.
Now let’s put those to use. Edit the Home page and drop in a Chart and a Classic Report to the body. The Chart will use REST Source AzureCustomerCountByCountry and the Classic Report will use REST Source AzureSalesHeader.
The Classic Report is very basic and includes the column list from the AzureSalesHeader Data Source.
For the Customers chart the Series Source Location should be set to Region Source so that it inherits the source from the Chart and columns can be selected.
With all that in place, run the application to see the Home page with the Chart and Classic Report. Note that by following the default application setup a login is required. Just use the same workspace developer username and password.
Conclusion
In theory ORDS REST Enabled SQL can be used with any database that you have a JDBC driver for which will run on Java 11. In practice, only Oracle RDBMS and Oracle Cloud MySQL is supported. Keeping that in mind, the capability of bringing in data from other databases, such as Azure SQL and using it in your APEX applications opens up a lot of integration possibilities.
What projects does this make possible for you now? I’d be really interested to hear about it.
In future articles I will cover synchronisation, joining local with remote data as well as going beyond just select statements.
Addendum to the original article…if you are looking for an approach that is supported you can create database links from Autonomous Database to Non-Oracle databases.
Peter's Random Topic Library 