Mostly Making oracle easy, mostly:
Search for:
In Database Stuff

APEX 19.2: Faceted Search

4 Mins Read

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

If you’re not already using APEX..you SHOULD be. And if you want to read the 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 built first, then I’ll show you how I got there.

Here is 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.

The cool part: I have NOT provided any SQL behind this 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

I’m going to describe how this works as Mike Hichwa explained it to me. In layman’s terms, there are 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 want to tweak the facets for 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.

Author

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

Related Posts

9 Comments

  1. Stirling Butcher Reply

    Thanks Etay….I was stuck with that case sensitive column name issue to. This was a strange place to find the answer but this is where my search lead me! 🙂

  2. Machelle Reply

    Quick question. I am building my view based on a sql instead of a table and when created it did not bring in and facets. I was able to create facets based on my code but am getting the following error.
    Column Organization_ID, referenced by facet P2_SEARCH, is not available or unauthorized.

    1.is_internal_error: true
    2.apex_error_code: APEX.FS.COLUMN_UNAUTHORIZED
    3.component.type: APEX_APPLICATION_PAGE_REGIONS
    4.component.id: 18504453149235509
    5.component.name: Search
    6.error_backtrace:

    Any help would be appreciated!! TIA

    • Etay

      Faceted Item -> Database Column -> name is case sensitive ( DB_NAME instead of db_name ) solved my issue ( APEX.FS.COLUMN_UNAUTHORIZED )

    • Marjan

      First of all, you have to be sure that the column you are referencing in the facet it’s included in the report columns. To be on the safe side, create report based on written SQL query, so you will be sure that all the columns you want are included. And second you use the same name in facet database column as report column.

    • Erman

      Make sure that your query is valid. Debug windows will give you clues about that. I used “PLSQL function returning SQL query” as region source and the produced query had an error after making some changes to one of the views/tables used in constructing the query. That makes all of the facets unavaliable.

  3. Mohidul Reply

    I’ve a little bit queries to know. I set the faceted item a radio group, Can I put faceted a value in PAGE ITEM. Any faceted Hacking?

Write A Comment