Generate functions for package constants

I

If you have a constants in a package you might want to expose them to your SQL queries. You wont able to access them from your SQL statements (for example from views or adhoc queries) unless you create a function for each of them and call that function instead of the constant.

With the generator your functions will be consistent, typo free and with correct datatypes.

Here is a generator for that:

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
    in_package_name         CONSTANT VARCHAR2(30)   := 'CONSTANTS';     -- source package with constants
    in_constant_prefix      CONSTANT VARCHAR2(30)   := '';              -- process just constants starting with this prefix
    in_fn_prefix            CONSTANT VARCHAR2(30)   := 'get_';          -- add optional prefix to functions
    in_generate_body        CONSTANT BOOLEAN        := TRUE;            -- switch between spec and body generator
BEGIN
    FOR c IN (
        SELECT
            LOWER(s.name) AS package_name,
            REGEXP_SUBSTR(LOWER(s.text), LOWER(in_constant_prefix) || '[a-z0-9_]+') AS constant_name,
            REGEXP_SUBSTR(UPPER(s.text), '\sCONSTANT\s+([A-Z0-9_]+\.?[A-Z0-9_]*(%TYPE)?)', 1, 1, NULL, 1) AS data_type,
            s.text,
            '%' || REPLACE(LOWER(in_constant_prefix), '_', '\_') || LOWER('% CONSTANT %') AS like_
        FROM user_source s
        WHERE s.type    = 'PACKAGE'
            AND s.name  = UPPER(in_package_name)
            AND LOWER(s.text) LIKE '%' || REPLACE(LOWER(in_constant_prefix), '_', '\_') || LOWER('% CONSTANT %') ESCAPE '\'
        ORDER BY s.line
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE(APEX_STRING.FORMAT('    FUNCTION %s%s', LOWER(in_fn_prefix), REPLACE(c.constant_name, LOWER(in_constant_prefix), '')));
        DBMS_OUTPUT.PUT_LINE(APEX_STRING.FORMAT('    RETURN %s', CASE WHEN INSTR(c.data_type, '%TYPE') > 0 THEN REPLACE(LOWER(c.data_type), '%type', '%TYPE') ELSE c.data_type END));
        --
        IF in_generate_body THEN
            DBMS_OUTPUT.PUT_LINE(APEX_STRING.FORMAT('    DETERMINISTIC'));
            DBMS_OUTPUT.PUT_LINE(APEX_STRING.FORMAT('    AS'));
            DBMS_OUTPUT.PUT_LINE(APEX_STRING.FORMAT('        PRAGMA UDF;'));
            DBMS_OUTPUT.PUT_LINE(APEX_STRING.FORMAT('    BEGIN'));
            DBMS_OUTPUT.PUT_LINE(APEX_STRING.FORMAT('        RETURN %s.%s;', c.package_name, c.constant_name));
            DBMS_OUTPUT.PUT_LINE(APEX_STRING.FORMAT('    END;', c.data_type));
            DBMS_OUTPUT.PUT_LINE('');
            DBMS_OUTPUT.PUT_LINE('');
        ELSE
            DBMS_OUTPUT.PUT_LINE(APEX_STRING.FORMAT('    DETERMINISTIC;'));
        END IF;
    END LOOP;
END;
/

You just call it once for specification, once for body and copy the content into your package.

And with some modifications you cound generate get and set functions to use similar approach for package variables.