• Skip to primary navigation
  • Skip to main content
  • Skip to footer

Storm Petrel, LLC

  • Storm Petrel LLC
  • Products
    • All Products
    • Tempest-GEMS
    • Tempest-Time | Time & Expense Tracking
  • Services
    • All Services
    • GSA
    • Customer Service
  • Blogs
    • Grants/Tempest-GEMS Blogs
    • Oracle Blogs
  • Privacy Policy
  • About

plsql

How to Query Oracle Font APEX

16 June 2021 by Christina Moore

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

Filed Under: Oracle APEX Tagged With: API, failure, json, json.stringify, mle, multilingual engine, OCI, oracle, oracle apex, Oracle Cloud Infrastructure, plsql, restful

Querying JSON with Oracle SQL

25 May 2021 by Christina Moore

Abstract

Querying JSON data within Oracle improves year by year. For nearly 50 years, Oracle has been a relational database relying on linking data between tables. JSON often contains arrays of data within elements. Querying JSON successfully relies on more than knowing syntax, but also understanding how the data structures differ. We will explore SQL select statements to parse JSON data including arrays. You’ll need to utilize these skills while developing Application Programming Interfaces (API).

This is part of a series of articles on developing and supporting API written with Oracle PL/SQL and Oracle APEX.

Keywords

Oracle Native JSON, Oracle JSON_TABLE, Oracle apex_json, plsql, plsql api, Restful API

Introduction and History

Parsing JSON data may be accomplished within an Oracle select statement. Years ago, we relied on parsing the data with APEX functions such as apex_json.parse and apex_json.get_varchar2. When I compare an older API I wrote in PLSQL using these apex_json functions to the current technique involving the select statement, we reduce the amount of code needed by 75%. Reducing code with the select statement reduces the risk of errors and reduces the cost of developing API.

One can observe a trend at Oracle that continues to include JSON as an important and ongoing source of data. With Oracle 12c, the support for native JSON improved. This was released in July of 2013. Publishing a blog about this in 2021 does not push the envelop on early adoption of technology. Instead, I indent to increase parity between the JSON datatype support with the increasing support of RESTful API.

RESTful API development and rapid parsing of JSON data must travel hand-in-hand.

I write with a slight bias that involves fetching data via a RESTful GET. In an effort to provide a single point of reference, I’ll touch on resources that permit hosting your own data in a RESTful format for others to perform a GET.

API Series

This article is part of a series of articles about developing an API with Oracle PL/SQL and Oracle APEX.

  1. APEX Debug Messages – standardizing error collection, debugging, and trace
  2. Oracle Pl/SQL Template for RESTful API

Structure

Nearly all of the Oracle documentation, guidance, and related blog posts start with the awkward premise of populating the data. I am going to skip this step. Finding interesting JSON data on the web is easier than creating JSON data. Additionally, creating JSON data from a blog post may introduce an error that will be impossible to find. Most of us that attend “Internet University” have experienced copying text from a website only to find unprintable (hidden) ASCII characters that waste an hour of troubleshooting. Sometimes even the process of pasting to a text-based note pad then copying won’t avoid these problems.

You can fetch some JSON data at the following links:

https://jsonAPI.org/examples/

https://api.publicapis.org/

And if you are reading this article, then you likely have an API target selected. Go pull data from there with your browser, cURL, Postman or something.

I tend to query JSON from our API_STAGING table. You can find this table structure at my github:

https://github.com/cmoore-sp/plsql-api/blob/f66d0e6865bdc2a40f40f0c7560ed6820bfb27d9/api_setup.sql

Table API_STAGING

As mentioned, you can copy/paste the API_STAGING table structure from the GitHub link above. This table includes a CLOB for JSON data. I do not put in the JSON integrity checks, just as a personal preference. Because we are working with is coming from a remote connection, I decided it is better to accept the data as presented then generate an error. It is easier to have the data then diagnose the issue. The option is to give an error before seeing the problem.

CREATE TABLE API_STAGING (
  STAGING_PK    NUMBER generated by default on null as identity, 
  SCHEMA_NAME   VARCHAR2(30), 
  API_NAME      VARCHAR2(100), 
  API_MODULE    VARCHAR2(100), 
  DATA_TYPE     VARCHAR2(100), 
  ACTION        VARCHAR2(100), 
  ACTION_DATE   DATE, 
  BASE_URL      VARCHAR2(1000), 
  APPEND        VARCHAR2(2000), 
  URL           VARCHAR2(4000), 
  STATUS_CODE   VARCHAR2(10), 
  JSON_RESPONSE CLOB, 
  HTTP_RESPONSE CLOB, 
  BODY          CLOB, 
  DELETE_OK     DATE );

This table is used for the data coming from a remote site. JSON data drops into the JSON_RESPONSE clob. In the event that we get errors or an HTML response, we put that into the HTTP_RESPONSE field. When preparing data to be pushed to a remote site, we can use the BODY blob for a payload. Our DELETE_OK field aids us with clean up which is done via a scheduler.job regularly. The table serves as a log for the RESTful activity between Oracle and the RESTful target.

When I find JSON I wish to explore, I copy the JSON into a new row and paste it in the JSON_RESPONSE clob. Now I don’t worry about typos (my own anyway). The second advantage is query/code I develop to explore the data will dovetail into my final product. I don’t have to re-do efforts. The query starts with:

select
  s.staging_pk
  from api_staging s,
    json_table (s.json_response, $ columns

JSON Data Types

As a courtesy and quick reference because JSON data types and Oracle data type differ, I’ll provide a quick list.

  • String
  • Boolean (true or false)
  • Number

Some list Arrays as a data type – fine, you are correct too. They are a data type in as much as JSON is a data type or Object is a data type. I’ll return to this in a bit.

Oracle does not have a boolean datatype within tables (at least not through 19c). Oracle 21C has numerous JSON features. We can explore these in another post during 2021. Furthermore, Oracle data fields are powerful and useful. JSON dates tend to be in the ISO-8601 format which we normally convert with a function based on this model:

return to_date(substr(P_JSON_DATE,1,16),'YYYY-MM-DD"T"HH24:MI') ;

Frankly, we store the JSON boolean data as varchar until we migrate the data further into our Oracle tables. In our intermediate staging tables, the data is hybrid of Oracle structures (VARCHAR2, NUMBER, DATE) and JSON stuff such as booleans.

JSON Objects & Arrays

I came to JSON with an Oracle brain that had spent years frustrated with poor solutions including CSV. My words and thoughts are wrong, I know that but when I see JSON Arrays I think “Rows”. When I see JSON Objects I think: Column. Sure, wrong and also not universal, but really close. So many people use arrays for small data sets like latitude and longitude.

The squiggle brackets define the limits of Objects. The square bracket provide limits for arrays. Here is a classic example (and a lovely example too) – note this is partial JSON and not expected to validate:

{
    "count": 834,
    "entries": [{
            "API": "Cat Facts",
            "Description": "Daily cat facts",
            "Auth": "",
            "HTTPS": true,
            "Cors": "no",
            "Link": "https://alexwohlbruck.github.io/cat-facts/",
            "Category": "Animals"
        }, {
            "API": "Cataas",
            "Description": "Cat as a service (cats pictures and gifs)",
            "Auth": "",
            "HTTPS": true,
            "Cors": "unknown",
            "Link": "https://cataas.com/",
            "Category": "Animals"
        }, {
            "API": "catAPI",
            "Description": "Random pictures of cats",
            "Auth": "",

These data came from https://api.publicapis.org/entries.

I love that the JSON object starts with a count. The array has the name “entries”. The array has the square bracket. Array elements are separated with comma. In this case, the phrase “arrays are rows” holds up. In some cases people use arrays to represent sets of data such the latitude and longitude as noted below. Yes, array! No, not really data rows.

    "geometry": {
        "type": "Point",
        "coordinates": [
            -71.049999999999997,
            42.350000000000001
        ]
    },

Querying JSON data

Numerous on-line articles and Oracle manuals provide great guidance on chasing down the dotted notation for data such as geometry.type

The challenge we’re constantly looking up within our own code is the managing of arrays. Some API provide a count or other meta data in the first elements. These first elements give you a handle to start picking data from. Many folks also name their arrays such as:

    "entries": [{
            "API": "Cat Facts",

The array name is entries so when doing the query, the query looks like this:

select 
  s.staging_pk                                       
  ,json.count
  ,json.api_name
  ,json.description
  ,json.auth
  ,json.https
  ,json.cors 
  ,json.link
  ,json.category
from api_staging s,
  json_table
  (s.json_response, '$' columns
    count    number		path	'$.count',
    nested path '$.entries[*]' columns
    (
      api_name      varchar2(255)   path '$.API'
      ,description  varchar2(255)   path '$.Description'			
      ,auth         varchar2(255)   path '$.Auth'  
      ,https        varchar2(255)   path '$.HTTPS'
      ,cors         varchar2(255)   path '$.Cors'
      ,link         varchar2(255)   path '$.Link'
      ,category     varchar2(255)   path '$.Category'	
    )
  ) json
 where api_module = 'api_openapi.entry_get' and delete_ok is null and status_code = 200
order by json.api_name

The $ indicates the starting point. The nested path dives deeper in to elements. The [*] creates a new line for each row within the array. The api_name is basically entries.api.

What if [{ is first?

Sometimes the JSON data does not contain meta data such as a count or the URI or other useful stuff at the front. Sometimes API authors do not name their arrays. When this happens, the first two characters in the JSON data are [{. We experienced this when writing an API for Okta. In this case, the Oracle syntax compresses to ‘$[*]’. Very difficult to perform an internet search for this situation and I find it hard to remember. The resultant query looks like this (an example from our Okta API):

 for j in (
  select     					
    json.okta_id
    ,json.status
    ,json.status_changed
    ,json.first_name
    ,json.last_name
    ,json.email
  from api_staging s,
    json_table
    (s.json_response, '$[*]' columns
      okta_id          varchar2(128)  path  '$.id'
      status           varchar2(128)  path  '$.status'
      status_changed   varchar2(128)  path  '$.statusChanged'
      first_name       varchar2(128)  path  '$.profile.firstName'
      last_name        varchar2(128)  path  '$.profile.lastName'
      email            varchar2(128)  path  '$.profile.email'
    ) json
  where staging_pk = P_STAGING_PK
) loop

Sample JSON data related to this query is (again this is a partial selection and will not validate as JSON):

[{
        "id": "00u2iy2tcgELZEoUR357",
        "status": "PROVISIONED",
        "created": "2020-01-17T20:30:51.000Z",
        "activated": "2020-01-17T20:30:51.000Z",
        "statusChanged": "2020-01-17T20:30:51.000Z",
        "lastLogin": null,
        "lastUpdated": "2020-01-17T20:30:51.000Z",
        "passwordChanged": null,
        "profile": {
            "firstName": "One",
            "lastName": "Two",

Multiple Arrays?

Sometimes the JSON data will map to two or more tables within your Oracle structure, classically a header and detail table. When examining JSON data we may find an array for the “header” data, then a nested array for the child or detail data. As we extract the data from the API staging table, we use nested loops with implicit cursors (for loops with a select statement).

When querying JSON data, we flatten out the complexity we see. Then we face a classic situation of remodelling the relational structure that was present within the JSON data and that we want in our Oracle tables.

Sometimes, we use one loop for the data set and track of the header data changed as we spin through the JSON array. It is both a matter of style and a matter of data presentation. While writing I am looking at an example of an invoice from an API by Recurly. The invoice has an invoice header and invoice detail (line items).

Here is code (it is a bit long, sorry)

for j in (
   select   
      object
      ,invoice_id
      ,account_id
      ,invoice_number
      ,subscription_id
      ,plan_id
      ,addon_id
      ,addon_code
      ,accounting_code
.... abbreviated
      ,line_item_id
      ,item_id 	
.... abbreviated
    from api_staging s,
      json_table
      (s.json_response, '$' columns  object	varchar2(128)		path '$.object'
         ,account_id      varchar2(255)   path '$.account.id'
         ,collect_method  varchar2(50)    path '$.collection_method'
         ,po_number       varchar2(50)    path '$.po_number'	
.... abbreviated	
         ,nested path '$.line_items.data[*]' columns
           (
            line_item_id      varchar2(128)   path '$.id'
            ,subscription_id  varchar2(255)   path '$.subscription_id'
... abbreviated
           )					
     ) js
  where staging_pk  = P_STAGING_PK
  order by invoice_id
) loop

Then as we spin through the rows, we test each loop to see if the header is the same as the prior one or new.

--
-- I N V O I C E   H E A D E R 
--
-- if the invoice_id is "new" (does not match the current), then insert or update the invoice header data
if j.invoice_id <> l_current_invoice_id then -- this is a new invoice header for this run of the code
  l_current_invoice_id := j.invoice_id ;
  l_loop_counter	:= 1;
  -- does the invoice exist?
  select count(*) into l_select_count from rc_invoice where invoice_id = j.invoice_id;
  -- convert the date
  r_invoice.invoiced_on	:= json_to_date(j.invoiced_on_json);
  -- invoice does not exist, so insert it
  if l_select_count = 0 then
.... continued

As we plod along we process each invoice detail row.

--
-- I N V O I C E   D E T A I L
--
-- we need the invoice_pk
select count(*) into l_select_count from rc_invoice where invoice_id = j.invoice_id;
if l_select_count = 1 then
  select * into r_invoice from rc_invoice where invoice_id = j.invoice_id;
else
  raise_application_error (-20000,'Invoice PK for: ' || j.invoice_id|| ' is null in: ' || l_procedure); -- blow up we can't be here without an invoice header row!
end if;

-- does the invoice line item exist?
select count(*) into l_select_count from rc_invoice_item where line_item_id = j.line_item_id;
l_item_pk 	:= item_pk(j.item_id);
l_start_date	:= rc_staging.json_to_date(j.start_date);
l_end_date	:= rc_staging.json_to_date(j.end_date);

The Right Way

There is no right way. First, JSON is not a protocol but a data format. We don’t get to argue right/not-right in such an environment. Furthermore, REST (representational state transfer) is an “architectural style”. It is not a protocol that require adherence to a limited set of rules. My answer is to play and have fun. Maybe your API host provides nice meta data and counters at the top. Maybe they name their arrays and keep the nesting of the data to something that doesn’t break my Oracle-centric brain. I see some 1980s and 1990s legacy dependency on code and loops in my style. And I see the value of embracing JSON. To each their strength.

Have some fun and stay safe.

cmoore

Filed Under: Oracle APEX Tagged With: Oracle apex_json, Oracle JSON_TABLE, Oracle Native JSON, plsql, plsql api

Footer

We are Storm Petrel

We are a team of professionals dedicated to designing, building, hosting, and supporting enterprise-class software applications using Oracle APEX and PL/SQL

Learn more about us.

Powered by Oracle

PO Box 96, West Halifax VT 05358

sales@storm-petrel.com

  • #979 (no title)
  • GSA Schedule Information
  • Blogs
  • Privacy Policy

Copyright © 2025 ·Storm Petrel LLC

  • #979 (no title)
  • GSA Schedule Information
  • Blogs
  • Privacy Policy