Creating APEX session from PL/SQL

I

I often use views with APEX items, so I can filter data ASAP, not after they are all loaded to the app. It is also a security layer like using contexts for VPD or views shadow schema. To test these views in SQL Developer is tough. Unless you create an APEX session first and set values to items you want to use.

Creating session

To access page/app items from APEX in PL/SQL you have to create APEX session first:

DECLARE
    in_user_id          CONSTANT apex_workspace_apex_users.user_name%TYPE   := '&USER_ID';
    in_app_id           CONSTANT apex_applications.application_id%TYPE      := &APP_ID;
    in_page_id          CONSTANT apex_application_pages.page_id%TYPE        := 0;  -- not important now
    --
    v_workspace_id      apex_applications.workspace%TYPE;
BEGIN
    -- find and setup workspace
    SELECT a.workspace INTO v_workspace_id
    FROM apex_applications a
    WHERE a.application_id  = in_app_id;
    --
    APEX_UTIL.SET_WORKSPACE (
        p_workspace         => v_workspace_id
    );
    APEX_UTIL.SET_SECURITY_GROUP_ID (
        p_security_group_id => APEX_UTIL.FIND_SECURITY_GROUP_ID(p_workspace => v_workspace_id)
    );
    APEX_UTIL.SET_USERNAME (
        p_userid            => APEX_UTIL.GET_USER_ID(in_user_id),
        p_username          => in_user_id
    );

    -- create APEX session
    APEX_SESSION.CREATE_SESSION (
        p_app_id    => in_app_id,
        p_page_id   => in_page_id,
        p_username  => in_user_id
    );

    -- set session things
    DBMS_SESSION.SET_IDENTIFIER(in_user_id);                -- USERENV.CLIENT_IDENTIFIER
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(in_user_id);      -- CLIENT_INFO, v$ views
END;
/

You can change the user later:

BEGIN
    -- overwrite current user in APEX
    APEX_CUSTOM_AUTH.SET_USER (
        p_user => '&USER_ID'
    );
END;
/

Accessing items

Once you have APEX session, then you can set and get page items (and application items).

BEGIN
    APEX_UTIL.SET_SESSION_STATE (
        p_name      => 'P100_ITEM_NAME',    -- item must exists
        p_value     => 'VALUE',
        p_commit    => FALSE
    );
END;
/

To get all page items for your application including their values set in your session, use this query:

SELECT
    p.page_id,
    p.item_name,
    APEX_UTIL.GET_SESSION_STATE(p.item_name)    AS item_value,
    --
    p.item_source_type,
    p.item_source,
    p.source_used,
    p.item_default,
    p.maintain_session_state
FROM apex_application_page_items p
WHERE p.application_id      = NV('APP_ID')
    AND p.page_id           <= 9999
    --AND p.item_name         LIKE 'P%'
ORDER BY 1, 2;

For setting and accessing application items you use same APEX_UTIL procedures/functions, just the dictionary view is different: apex_application_items. You should always explore Oracle dictionary:

SELECT a.apex_view_name, a.comments
FROM apex_dictionary a
WHERE a.column_id = 0
ORDER BY 1;

Check this APEX package (set_item, get_item) and SESS package (specially create_session procedure) for more details and ideas.

Check this article about performance issues when using APEX items.