Storm Petrel, LLC

restful

How to Query Oracle Font APEX

by

Abstract

This is an article about using classic Oracle database tools to query Font APEX as a dataset. This article is also about failure, my failure(s) in achieving this goal. If you don’t care about the journey, the frustrations, the cool news tools I explored, just click to the RESTful link to get FONT Apex as JSON. (link https://g2235c8c2b07256-cmoore.adb.us-ashburn-1.oraclecloudapps.com/ords/cm/font/apex)

I did explore Oracle 21C, Multilingual Engine (MLE), Javascript, JS variables, and a whole lot of other fun stuff for days and days before writing too much code to solve a simple problem that I had in 2017. In short, I failed. Ok, sure I got the data I wanted but I failed at elegant, efficient, pretty. I may have learned something including that writing about failure might be fun?

Challenge

Please go do this then show me how easy and elegant it ought to be. Then ping me back with a positive and warm vibe on the Twitter (@cmoore_sp). Hey trolls, I already admitted I flopped and I got stuff to learn.

History

During the summer of 2017, our team supported a Toronto-based firm with custom application to help with quality assurance work. I thought it would be cool, when during setup and management of the application that the client could pick their own icons for various tasks that they setup in their QA tracking. Oracle is a terrific database. APEX, or Application Express, is a low-code rapid development tool. Sadly, that moniker under-represents the power of APEX. Our firm has managed billions of dollars with APEX and 400,000 PDF documents within a single application with 2,000 users engaging in daily tasks using both English and Spanish. APEX has serious capabilities.

I wasn’t successful at finding a view or a synonym with the information. When I digested the Universal Theme sample app, I got little more information there. I wrote Christian Neumueller (Twitter: @chrisNeumueller) with the Oracle APEX team in July of 2017 with the question. He wrote back stating that there was nothing “server-side” – meaning nothing in the database itself. Font APEX is thus buried within the labyrinth of CSS and JS.

I extracted the data I needed with expedience and zero grace – and decided I needed to explore further. Since then, I’ve deployed into Hurricane Harvey in Texas, then deployed to Puerto Rico for two hurricanes (Irma and Maria). For the last 16 months, our team has been supporting various COVID funding initiative with our software – y’know a global pandemic.

Four year later, I still want an ability to query APEX font choices with classic database tools. As I returned to the question, I also recognize I have absolutely no NEED to do this stupid task. I sure have spent days and days trying.

Objectives

I started this project in 2017, then again in May of 2021 with a few objectives.

  1. Do something cool that results in code worthy of praise, recognition, and challenges the boundaries of my knowledge – and may teach someone else something.
  2. Learning something cool along the way myself – push myself
  3. Publish both code and Font APEX as a data that can be refreshed with a click.

Successes

  • I pushed my knowledge and did learn stuff.
  • I pushed myself into 21C and JavaScript and Multilingual Engine (MLE).
  • Published a RESTFul link for Font APEX.

But I really consider my effort a failure, a flop, and a frustrating fizzle into the desire to publish even if that means recognizing I needed help long before I gave up.

Failure

Following the series of API articles and efforts I have published in the recent years, I thought I put up an APEX application with an API that fetched Font APEX dynamically making it available in an Oracle table and/or a JSON dataset.

Playing Along At Home

If you are going to follow along at home, please install the Universal Theme Sample Application on your own system. You can do this from the APEX main application page, click “Create>”, then click the Productivity App card. From there, install the “Universal Theme Sample Application”. It takes only a few minutes and as you explore and hack, you won’t be on someone else’s server whackin’ away. It is just more respectful to your neighbors and friends. We have an APEX workspace we call “Backyard” in which we play.

APEX Team Answer

In July of 2017, I got this in an email from the APEX team at Oracle:

You could also write some script to generate records from images/themes/theme_42/1.1/js/demo/icons.js, or use that (probably just the global JavaScript variable apexIcons) in your app’s UI to generate the “pick an icon” dialog.

APEX Team at Oracle

I haven’t a clue what I did 3 hurricanes and 1 pandemic ago. I do know we delivered an application with a table that included APEX fonts.

I acknowledge I am a JSI – JavaScript Idiot. Soy idiota. This handicap came about in the mid-1980 when I was learning programming. I resisted the computer science degree which focused on big iron mainframes. Instead, I learned Borland’s Turbo Pascal in independent study. Clancy and Cooper’s book “Oh, Pascal!” sits proudly on myself behind me. The syntax and structures I learned in the early 1980s subsequently became the foundations for our PL/SQL. I’d learned BASIC in high school on a PDP-11. I am clumsy and awkward with object-oriented languages.

We all must learn to celebrate diversity, mustn’t we.

I knew Christian left me a serious clue in his email. The clue is still there, ignored by me.

Step 1 – API

I had a plan! I can readily find the icons.js. I thought I could automate that a bit. The May 2021 (APEX 20) path, if you have the Universal Theme Sample Application installed, is something like:

/i/themes/theme_42/1.6/js/demo/icons.js

In July of 2017, it was:

/i/themes/theme_42/1.1/js/demo/icons.js

I see a pattern. We have a global substitution to get to the ‘/i/’ in APEX (#IMAGE_PREFIX#).

With a too-quick look I see squiggle brackets, square braces, and assume immediately I am looking at JSON. Sweet.

I pull out my trusty API template (see article: https://storm-petrel.com/2021/05/21/oracle-plsql-api-template/). With in a few minutes, I have pulled the data into my API_STAGING table as an HTTP Response (a clob).

Fail

Several of you playing along at home already know what I did not know. The icons.js is not JSON but a JavaScript variable or JS Object. I don’t know how to parse that. There has to be a way! I can admit I wrote some bad code in PL/SQL that tried – and failed.

Step 2 – JS Object to JSON

With the minor setback of not knowing much about JS variable, I rapidly developed a new plan.

I must learn how to Stringify an JavaScript object into my familiar JSON. Then I’ll win and move forward. I can now Stringify safely because I learned this last month.

<script>  
var icons= JSON.stringify(apexIcons);
document.write(icons);
</script>

I read a blog post by Joel Kallman that he posted in November of 2018 discussing Oracle Database Multilingual Engine (MLE) that is now available to us in 2021, as in it is in Oracle release 21c.

Great, toss my JSON.stringify into familiar PL/SQL code using Oracle’s MLE.

Now I am on the leading edge! Watch me go now. I just need 21C which came out in 2021 (clever name, eh?). Ok, but our team just got fully stable on 19C after a miserable few months, so I can’t ask us to upgrade.

This gives me an excuse to setup a project on Oracle Cloud Infrastructure (OCI) with the always-free option. I pop my API template up. Install the Universal Theme, fetch down a lovely CLOB filled with my JavaScript Object (variable). Because I am a database person, I know well how to do a SELECT on data. Christian’s hint about the “global JavaScript variable apexIcon” is still floating in the stupid corner of my brain. I don’t know how to connect that phrase with “write a script”.

Give me a minute on 21C with MLE, a goodly handful of brilliant articles, and I’ll master this task.

For those wincing at home, I have wasted significant number of hours, even days at this point.

I give Oracle my personal credit card to setup my free OCI session – and that has gotten easier over the years. Cheers to Oracle for streamlining that and working at catching up. Well done, you.

I write a bit of code with my new-found friend MLE then put it into SQL Developer linked to my Oracle Cloud. It runs perfectly but I don’t see anything. I look carefully at the blog posts to see folks using APEX SQL Workshop “SQL Commands” to run this stuff. Ok, I haven’t been in SQL Commands since 2015. Copy/paste, run!

DECLARE
   ctx      varchar2(50);
   l_js     clob;
   l_var    clob;
BEGIN
    l_js := q'~
var apexIcons=
{
	ACCESSIBILITY:[
		{name:"fa-american-sign-language-interpreting"},
		{name:"fa-asl-interpreting"},
		{name:"fa-assistive-listening-systems"},
		{name:"fa-audio-description"},
		{name:"fa-blind"},
		{name:"fa-braille"},
		{name:"fa-deaf"},
		{name:"fa-deafness"},
		{name:"fa-hard-of-hearing"},
		{name:"fa-low-vision"},
		{name:"fa-sign-language"},
		{name:"fa-signing"},
		{name:"fa-universal-access"},
		{name:"fa-volume-control-phone"},
		{name:"fa-wheelchair-alt"}
	]
};
    var gfg = JSON.stringify(apexIcons);
    console.log(gfg);
    console.log("Goodbye Cruel World!");
    ~';
   ctx := DBMS_MLE.create_context();
   DBMS_MLE.eval(ctx, 'JAVASCRIPT', l_js );	
   DBMS_MLE.drop_context(ctx);
END;

I am scriptifying!

So close. And fail!

Fail

I need to fetch my APEX Icon data. We’ve established I don’t know how to dance the JS rhythm. And I know I have the JavaScript object warm and happy in my Oracle Table API_STAGING.HTTP_RESPONSE. I’ll just query that plop that in there, stringify objects, then I’ve won the game.

I can’t even inventory my attempts, the hours are lost to me forever. I roll into an APEX page to play there only to blow error after error. Eventually I suspect my data. A query with dbms_lob.getlength informs me that I have 44,036 characters in my clob. This exceeds my 32,767 limits on varchar2 stuff, good ole 2 to the 14th power. Ugh.

Achievement

Ok, I scriptified within PLSQL. There was no particular object-oriented elegance to the effort. I do have a real job that requires hours, effort, and attention. So far, I’ve burned at least 4 days on this effort. I must return to my real job.

RESTive

Brute-force programming results in ugly and longish code. It is also embarrassing. I’ll not publish my mess. I do have some ego left. While many of my efforts with API normally result in a restful state for myself, my writing, and even for data. This process left me entirely restive and frustrated.

Instead, I’ll publish the results:

https://g2235c8c2b07256-cmoore.adb.us-ashburn-1.oraclecloudapps.com/ords/cm/font/apex

Oracle PLSQL Template for RESTful API

by

Abstract

A key purpose of this blog entry is to communicate that we are publishing a template for PL/SQL-based API. It is located on my GITHub page (https://github.com/cmoore-sp/plsql-api.git) and communicates with a public website hosting public APIs.

Introduction

My first interesting exploration with writing API with Oracle APEX and PL/SQL involved exchanging data and documents between our database and AWS S3. It has been four years since I published that effort. In the ensuing years, our team continued to write API. We have benefited and appreciated the progressive improvements that Oracle made with writing these digital connectors. Release by release, year by year, Oracle smooths the process of connecting via HTTP and managing JSON data. In APEX 20.1, the team introduces us to APEX based wizard to exchange data via HTTP links.

A key purpose of this blog entry is to communicate that we are publishing a template for PL/SQL-based API. It is located on my GITHub page (https://github.com/cmoore-sp/plsql-api.git) and communicates with a public website hosting public APIs. I initially worked through the US Weather Service’s site. But when I hit it from Oracle, it denied me for the lack of authentication whereas I easily got data from Postman. C’est la vie. We’ve been using and refining this template for about 18 months, maybe longer.

Rarely is our goal writing code line by line. I reviewed our own work related to a data exchange link to the U.S. government’s FEMA data site. The version I wrote with the best tools at hand required 545 lines to parse JSON data. In 2021, that same effort took 124 lines. A quarter of the code to achieve the same results. The code is simpler, shorter, and easier to read. That all boils down to fewer errors and reduced development costs.

This is the first of a series blog posts related to writing application programming interfaces with Oracle PL/SQL. As I write this, I celebrate the APEX based efforts, which reduces code even further.

This blog is part of a series. The first dealt with error trapping and debug logging. That can be found here: Oracle APEX Standardize Error & Debugging

Design and Build an API with Oracle

Early Days

Step 1 research and explore. And do your early explorations with tools such as cURL or Postman or some tool other than Oracle. Sure, Oracle is the final destination so the data can mix happily with your tables. Tools like Postman provide better debugging and visibility into the feedback from the API host.

Frankly, the error messaging from Oracle is a bit too blunt – non-specific and incomplete. Postman-like tools provide granular assessments including status (200-OK), time, size. Postman provide a cURL version of the effort and the full suite of headers is visible. It is a Leatherman or Swiss Army knife of utilities.

Furthermore, given many of us use Oracle from a cloud-based site, launching API tests from there (AWS or Oracle Cloud or whereever) introduces several other layers of variables and potential issues such as:

  • Oracle-based Access Control Lists (ACL)
  • Oracle’s management of HTTPS / SSL certificates
  • Routing issues within the cloud environment
  • Cloud-based Access Control Lists
  • Cloud-based Firewalls and Web Application Firewalls

For example, our WAF denies stuff that looks like SQL – thinking it may be seeing an SQL injection attack. Scraping the Oracle’s 1980’s bias and the cloud-edge from early work saves those problems for later. Maybe it is all perfect, but in the early hours limit your variable and source of trouble.

While on Step 1 “Research and Explore”, read those API instructions carefully.

At Storm Petrel, we look for the ‘Long Pole in the Tent’ or that deep dark hole that will trap the effort. Somewhere in the API documentation and in your effort there is easy work and tough work. Something will hold the entire effort up – that dreaded long pole.

I tend to be suspicious of the authentication process. We tackle that first. We tried for months to nail down an API with PayPal and got utterly frustrated, so frustrated with shopped for another vendor. When we did, we researched the API documentation and access as an integral part of the purchase decision.

The cost of building and supporting an API ought to be part of the long-term analysis of selecting a remote partner. Many vendors provide a sandbox or developer’s access to the API. We pursue that before plonking down a credit card on recuring costs – well we did after the PayPal mess.

After years of writing and supporting API with Oracle PL/SQL and APEX, we state to ourselves – if it can be done with Postman it can be done with Oracle.

API Workflow and Table Architecture

With few exceptions, like my partner’s application to check the weather outside is window and the pool temperature, data from the outside ought to be buffered and isolated as it transitions into your curated databases. It is foreign data why trust it explicitly?

Our workflow and architecture goes as follows (when getting data from a remote site)

  1. Execute the HTTP GET
    1. Store the entire GET syntax in a table called API_STAGING
      1. API Name,
      2. Module name,
      3. Base URL
      4. Appended bit of the URL
      5. HTTP status code
      6. JSON response
      7. HTTP response
      8. Ok to Delete (a date field)
    2. A HTTP failure also stores the API staging data.
    3. This packages manages the API, and looping needed to fetch a large number of data rows, and error trapping.
  2. Parse the JSON data
    1. Using a loop, spin through the API_Staging data
    2. Query the new JSON data
    3. Merge data to intermediate Oracle table
      1. We create a table based on the JSON data
      2. We name fields as close as possible to the JSON names
      3. We add a few fields to dates of updates and add – basic management
    4. Mark rows in the API_Staging table that they can be deleted after the JSON data has been successfully placed into Oracle tables as needed
  3. Merge with the real data inside the application
    1. We can validate primary keys and foreign keys
    2. Confirm integrity of the data
    3. Isolate and report on any anomalous data

When updating remote data with a POST or PUT, the process goes in reverse. We progressively convert the data and log each action. When ready, we put the out-bound data (a blob or clob) in the API staging table.

Tables

  • API Staging – tracks the API activity, behaves like a log for API calls and responses
  • Intermediate Tables – tables that mimic the JSON data structures but using classic relational database tools. We mimic JSON field names where possible.
  • Production Tables – These tables become the destination for the API data (or the source if sending out with a RESTful POST or PUT)

Data Merge

Historically, I have been comparing a few key elements of data to test if a change exists. Some API, such as FEMA, includes their last update date and a hash value. I can easily compare these. After examining work done by the Oracle APEX development team, I noticed that they are doing an MD hash of an entire row of data. I lifted their procedure into some tests recently.

They converted dates/timestamps to varchar2. They also converted numbers to varchar2. The first time I used their process, I got snagged by null values in fields. I updated the procedure to include an NVL. Now, new data gets inserted. Data that appears the same, I run through hash it, then compare old and new.

function api_publicAPI_MD5 (
  P_ROW  in api_publicAPI%rowtype
) return varchar2
as
  l_procedure	  varchar2(100) := 'api_publicapi_staging.api_publicAPI_MD5';
begin  
  -- please convert all values to VARCHAR with consistent formats and manage nulls
  -- such as to_char("P_ACTION_DATE",'yyyymmddhh24:mi:ss'), etc
  return apex_util.get_hash(
    apex_t_varchar2(
     nvl(P_ROW.api_name,'^')
     ,nvl(P_ROW.description,'^')
     ,nvl(P_ROW.auth,'^')
     ,nvl(P_ROW.https,'^')
     ,nvl(P_ROW.cors,'^')
     ,nvl(P_ROW.link,'^')
     ,nvl(P_ROW.category,'^')
     ));		
end api_publicAPI_MD5;

Template for API

As mentioned above, I am publishing an API template written in PL/SQL on my Github site. I endeavored to find an API site that did not require registration or authentication so that this package could run easily. API sites that are fully open run the risk of denial-of-service attacks. I respect the need for authentication. I did not include much guidance on authentication. We’ve rarely encountered the same rules twice. We plod through that effort after registering an account. Sorry.

The files include:

1. API_SETUP (procedure)

This procedure establishes a couple of tables

  1. API_STAGING for the logging of API data in a native format
  2. API_PUBLICAPI mimics the Public API data structure. Sorry for the awkward name. Our standard involves starting with “API_” then the name of the destination. In this case the destination is “PublicAPI”

2. API_PUBLICAPI_PKG

Again sorry about the stupid name. Our standard is API_Destination_PKG so when the damn thing is called PublicAPI, it looks odd. This package does the HTTP GET and stores the resultant data in the API_STAGING table. The package includes standard code for all normal aspects of a RESTful API. It also references a proxy. In some cases, we use an Apache server to proxy our calls. This permits us to bypass the misery of the Oracle certificate management stuff.

3. API_PUBLICAPI_STAGING

This package page loops through the API_STAGING table to parse the JSON data into an Oracle table. It has a few tricks for querying JSON data.

Close

I will cover detailed elements in upcoming blogs. We’ve have learned and struggled with JSON queries. Some of the features are not well documented.

Have fun, be safe.

Christina Moore (@cmoore_sp)