Storm Petrel, LLC

multilingual engine

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