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)
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.
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.
I started this project in 2017, then again in May of 2021 with a few objectives.
- Do something cool that results in code worthy of praise, recognition, and challenges the boundaries of my knowledge – and may teach someone else something.
- Learning something cool along the way myself – push myself
- Publish both code and Font APEX as a data that can be refreshed with a click.
- I pushed my knowledge and did learn stuff.
- 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.
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:
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.
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:
In July of 2017, it was:
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).
Step 2 – JS Object to JSON
With the minor setback of not knowing much about JS variable, I rapidly developed a new plan.
<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.
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!
I am scriptifying!
So close. And fail!
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.
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.
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: