I’ve heard quite a bit about this killer, new feature in Oracle Application Express (APEX) version 19.2..so I wanted to give it a go, and share it with you folks.

If you’re not already using APEX..you SHOULD be. And if you want an official overview and announcement of this feature, I recommend this blog post from Carsten.

So what is Faceted Search?

Have you shopped for plane tickets lately?

The search criteria in the red box, those are ‘facets.’

Now, how cool would it be if you could browse your data with a similar approach? Without writing any code?

APEX and Low Code for the win

I’ll show you what I made happen first, then I’ll show you how I got it next. Here’s my App’s ‘Search’ page – it has Facets I can use to ‘narrow down’ my data as i explore it, just like when I was searching Flights. I have NOT provided any SQL behind that part of the application – APEX is doing that for me.

Point, click, gain insight.

Side note – Did you know that Oracle has a new motto/mantra? I think it gets to the root of what tools like APEX and SQL Developer are all about.

Our mission is to help people see data in new ways,
discover insights, unlock endless possibilities.

— Larry Ellison

This faceted search feature really does allow me to see my data in new ways.

How it works

Please take this with a grain of salt, as I’m describing the feature as Mike Hichwa explained it to me, so in layman’s terms – there’s some rules coded into APEX to look for certain types of columns (number fields for example) and to discount or avoid other types of columns (foreign keys and primary keys). When you use the New App wizard, and include a Faceted Search page, APEX will automatically look for and add some Facets to the page based on these rules. This way you can see it working out-of-the-box.

THAT is the ‘no code’ part.

Now, of course I can go in and change/add/remove the Facets as I’d like – and I will probably need to make my app ‘PERFECT’ for my end users.

Adding a Facet

I want to look at the kinds of beer I’ve been drinking…maybe based on the Month in which I’m enjoying them. So to get started, the query I have power the page is going to have a TO_CHAR(DATE_FIELD, ‘MON’) in the SELECT. I can then tie my Facet to this query column.

Now, you CAN have a DATE or TIMESTAMP field as a Facet, but APEX won’t add those by default, and when you DO add them, you’ll be limited to a RANGE facet type. I wanted check boxes for Month, hence my query to convert the DATE to a MON string.

Adding a Facet is extremely easy. Just ‘right click.’

The New Application Wizard gives you facets to start with…

Now I just need to fill in the details, but basically:

  • the COLUMN…obviously
  • the type of facet – you want check boxes, radio, range…
  • and for those facets, do you maybe also want a count of rows

So for my MONTH facet, I can define it thus:

I took some screen grabs and cobbled them together so you could see it all together easily.

Wait, wait…start over. How did you build this?

Easily. I started with some CSV data.

From a file…

Check over the data preview and new table column definitions…

you can go to an existing table as well

Cross your fingers…

9 out of 10,000 ain’t too shabby.

Click ‘Create Application,’ and then you’re going to pick the Pages you want included.

Obviously we want the ‘Search’ one.

Generate and run the program, and voila!

Some of these facets I dig, some I don’t.

Removing and adding a facet to my search page

via GIF

Took me 90 seconds, imagine how much faster you’ll be at this 🙂

Remember, APEX is super accessible.

It’s included in your Autonomous Database Cloud service – which is also now available FOR FREE.

And it’s available for any Oracle Database running pretty much anywhere. Go to https://apex.oracle.com/en/ to get started.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

Write A Comment