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.
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; /
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