Selecting Font APEX from a Database Table

Author: Christina Moore

Date: 22NOV2022

Abstract

For those of us that work databases applications from the server-side and feel less sophisticated with client-side activities, I wanted to keep an inventory of the icon available in Oracle APEX to users within standard database tables. Why would a software developer include fonts in a column? Imagine scoring with a star-based rating system, or the simplicity of thumbs up/thumbs down.

The process involved the following steps:

1. Find the CSS that includes the fonts
2. Copy the contents of this CSS to a text file (a CLOB field in a table)
3. Process the CSS data extracting the font name and font parameters
4. Save font name and font parameters to a database table.

The code and tables for this are located at my Github. Jump over there to save a bit of typing:

https://github.com/cmoore-sp/font-apex

Step 1: Find the Oracle APEX Font CSS

Go find your favorite APEX application. Find an icon on that favorite application, then right-click and inspect elements. In the image, I should a way of seeing the CSS file name and location. I want to discourage people from hitting well-known public sites. Bad behavior by well-intentioned developers is a bad thing. If you are reading this, then you are writing an APEX application, supporting an APEX application, or a student. So please, as a good neighbor, find your own CSS. I’ll blur out the sources I used but many will recognize the site.

With the link visible, right-click again and open in a new browser tab.

The results ought to resemble the following:

Step 2: Copy CSS to a CLOB

On Github, I published a small package that includes a standard table we use for the management and monitoring of API activity. I’ll provide details of this table in Github as well. Frankly, the table doesn’t matter one bit. It is just nice to have the CSS text in a database field that you can run the code on. So for purposes of simplicity, please take the CSS text from Step 1 and paste it into the table API_STAGING.BODY. Do all the cool database stuff like updating the field and committing.

Step 3: Process the CSS Data

The CSS is not JSON structured data that APEX can readily parse.  And if you use the wrong formatter, you can introduce spaces [chr(32)]. Best to not format it or touch this text file too much. With the CSS/font information stored in a clob in a known table, such as API_STAGING, then you can process through the data.

The data looks like this…

.fa-automobile:before{content:"\f1b9"}.fa-bank:before{cont

The font name followed the point/dot/period/decimal. And it is followed by a ‘:before’. The trick is to identify these bits in a search string that loops through the entire CSS.

		l_start := instr(l_font_css,'}.',l_start) + 2;
		l_end	:= instr(l_font_css,':be',l_start);

Here we use the colon-BE (:be) as the end of the phrase with the squiggle and dot as the start. We need to know the length of the text we are “snipping” from the text file.

		if l_start < l_end then
		  l_snip_length :=  l_end - l_start;
		else
		  l_snip_length := 4000;
		end if;
		l_font_name  := dbms_lob.substr (
		  lob_loc => l_font_css,
		  amount  => l_snip_length,
		  offset  => l_start
		  );

A quick test to discover if we are holding a real font. Does it start with fa- ?

if l_font_name like 'fa-%' then

Step 4 – Save the Font name to a database Table

Once we have the font name, then we can save it to a table, such as LU_FONT, then nibble off the next bit of text.

select count(1) into l_select_count
  from lu_font
  where font_pk = l_font_name;
if l_select_count = 0 then
  insert into lu_font (
		font_pk
	) values (
	l_font_name
	);
end if;

Conclusion

Three people would come up with three ways of executing this. And some would use clever client-side techniques. Yay. I like offering clients APEX fonts to use within data. Combine this with a color selector, and you’ve handed over some nice power to your users with little risk.

select * from lu_font;

• Category: Uncategorized