Bulk expose your package constants to SQL

T

Typically you have some constants in packages. And sooner or later you need to use these constants in SQL statements outside of the package, like in a view. Sadly even the latest Oracle 23ai won't help you with this. The solution is to create a function for each constant, which can be very tedious if you have many constants.

I have created a generator to automate this, but here is a different approach. We have can utilize the USER_SOURCE view to get the constant value and the USER_IDENTIFIERS view to localize constants in the package. With this approach you can also expose constants from the BODY.

Here is the function:

CREATE OR REPLACE FUNCTION get_constant (
    in_package          VARCHAR2,
    in_name             VARCHAR2,
    in_prefix           VARCHAR2        := NULL,
    in_private          CHAR            := NULL
)
RETURN VARCHAR2
RESULT_CACHE
AS
    out_value           VARCHAR2(4000);
BEGIN
    SELECT
        NULLIF(
            REGEXP_REPLACE(
                REGEXP_REPLACE(
                    REGEXP_REPLACE(
                        LTRIM(SUBSTR(s.text, INSTR(s.text, ':=') + 2)),
                        ';\s*[-]{2}.*$',
                        ';'),
                    '[;]\s*',
                    ''),
                '(^[''])|(['']\s*$)',
                ''),
            'NULL')
    INTO out_value
    FROM user_identifiers t
    JOIN user_source s
        ON s.name               = t.object_name
        AND s.type              = t.object_type
        AND s.line              = t.line
    WHERE t.object_name         = UPPER(in_package)
        AND t.object_type       = 'PACKAGE' || CASE WHEN in_private IS NOT NULL THEN ' BODY' END
        AND t.name              = UPPER(in_prefix || in_name)
        AND t.type              = 'CONSTANT'
        AND t.usage             = 'DECLARATION'
        AND t.usage_context_id  = 1;
    --
    RETURN out_value;
EXCEPTION
WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

I would suggest to create one function for strings, other two for numbers and dates so you can avoid implicit data type conversions.

This approach can be very helpful if you want to just create some adhoc queries or if your project is small and you are lazy. These dictionary views might be a bit slow, so you might consider using the materialized view for the query.