Application settings in APEX

E

Every app needs some sort of settings. If you are looking for a pure APEX solution, you should check APEX_UTIL.SET_PREFERENCE procedure. You might have guessed it, my solution is based on a custom (home made with love and passion) table Settings from project CORE.

Let's check the table definition:

CREATE TABLE settings (
    app_id              NUMBER(4)       CONSTRAINT nn_settings_app_id   NOT NULL,
    setting_name        VARCHAR2(30)    CONSTRAINT nn_settings_id       NOT NULL,
    --
    setting_value       VARCHAR2(256),
    setting_context     VARCHAR2(64),
    setting_group       VARCHAR2(64),
    --
    is_numeric          CHAR(1),
    is_date             CHAR(1),
    --
    description_        VARCHAR2(1000),
    --
    CONSTRAINT uq_settings
        UNIQUE (app_id, setting_name, setting_context),
    --
    CONSTRAINT ch_settings_is_active
        CHECK ((is_numeric = 'Y' AND is_date IS NULL) OR is_numeric IS NULL),
    --
    CONSTRAINT ch_settings_is_date
        CHECK ((is_date = 'Y' AND is_numeric IS NULL) OR is_date IS NULL)
)
STORAGE (BUFFER_POOL KEEP);
--
COMMENT ON TABLE  settings                      IS 'List of settings shared through whole app';
--
COMMENT ON COLUMN settings.setting_name         IS 'Setting ID';
COMMENT ON COLUMN settings.setting_value        IS 'Value stored as string';
COMMENT ON COLUMN settings.setting_context      IS 'To allow multiple values depending on context value';
COMMENT ON COLUMN settings.setting_group        IS 'Group just for grouping set in APEX';
COMMENT ON COLUMN settings.is_numeric           IS 'Flag to convert value to number';
COMMENT ON COLUMN settings.is_date              IS 'Flag to convert value to date';

Nothing special, just PK is missing and I have UQ constraint instead. That's because of the contexts I will discuss later. And there are some flags for data type checks and conversions.

Frontend

Let's check the Setting page in APEX after entering first few settings:

We have settings in a table. Now what? How can we use it?

I prefer a package (let's name it SETT). I expect to have all rows/names from Settings table available as functions so I can use them in views or in a source code. And I don't mean a generic function like SETT.GET_SETTING('NAME'), I want SETT.GET_NAME() to avoid typos and to ease search for dependencies. And I don't want to write these functions manually. So naturally I created a generator for them, you can check rebuild_settings procedure in app_actions package, it is quite simple.

So when you add/change some settings, you will get a warning icon for each name/row which is missing a function in the SETT package. You just hit Rebuild button and the rebuild_settings procedure will take care of it for you. You will see that warnings are gone.

Rebuild will also invalidate the result cache.

And you will also notice a new package in your schema with 3 functions. Note the data type conversion to NUMBER on the 3rd.

The reference columns in the grid (Procedures, Views) will show you the number of references to the current function so you can identify non used settings.

Groups is for grouping related names/rows together vertically and visually for better overview.

Generated package specification:

CREATE OR REPLACE PACKAGE sett AS

    FUNCTION get_test_a (
        in_context      settings.setting_context%TYPE := NULL
    )
    RETURN VARCHAR2
    RESULT_CACHE;

    FUNCTION get_test_b (
        in_context      settings.setting_context%TYPE := NULL
    )
    RETURN VARCHAR2
    RESULT_CACHE;

    FUNCTION get_test_c (
        in_context      settings.setting_context%TYPE := NULL
    )
    RETURN NUMBER
    RESULT_CACHE;

END;
/

You can check available functions with this query:

WITH
    FUNCTION get_package_name
    RETURN VARCHAR2 AS
    BEGIN
        RETURN app_actions.settings_package;
    END;
SELECT
    a.object_name,
    a.data_type
FROM user_arguments a
WHERE a.package_name    = get_package_name()
    AND a.position      = 0
ORDER BY 1;
/

Contexts

This should be plenty for a simple app. But I often have requirements to have different settings/values for same name depending on some condition (context) and even with some default fallback. That's where the setting_contexts table comes in.

CREATE TABLE setting_contexts (
    app_id              NUMBER(4)       CONSTRAINT nn_setting_contexts_app_id   NOT NULL,
    context_id          VARCHAR2(64)    CONSTRAINT nn_setting_contexts_name     NOT NULL,
    context_name        VARCHAR2(64),
    description_        VARCHAR2(1000),
    order#              NUMBER(4),
    --
    CONSTRAINT uq_setting_contexts
        UNIQUE (app_id, context_id)
)
STORAGE (BUFFER_POOL KEEP);

You set your contexts like regions, departments, roles... whatever you need. I created two regions:

After a page refresh you will get an extra region Context Values which will show you your settings with contexts as an editable pivot grid. All contexts will be presented as columns so I (and every administrator) can easily compare them and avoid mistakes. You can adjust their order via order# column.

After adjustment of TEST_A name for REGION_A context...

You should be able to access your values through:

SELECT sett.get_test_a()            AS value FROM DUAL UNION ALL    -- returns VALUE_A
SELECT sett.get_test_a('REGION_A')  AS value FROM DUAL UNION ALL    -- returns VALUE_AAA
SELECT sett.get_test_a('REGION_B')  AS value FROM DUAL UNION ALL    -- returns VALUE_A
SELECT sett.get_test_a('REGION_C')  AS value FROM DUAL;             -- returns NULL