How to query APEX collection data from database

How to query APEX collection from database

Oracle APEX has APEX_COLLECTION utility to store data per session, which is really useful when we need to store data temporarily and can be posted after the performing validations/ calculations.

APEX collection is so nice however we can access the values only within session and if you look closely APEX collections are only accssible from APEX session state.

I have struggled a lot to debug APEX_COLLECTIONS whenever i needed to do calculations and complex logic writing untill my friend and colleague Amit Srivastav gave me one magical PLSQL code block which can bring all the collections values to your PLSQL Developer/ SQL developer or Toad.

DECLARE
   v_ws_id        apex_workspaces.workspace_id%TYPE; -- Workspace ID
   v_app_id       NUMBER := 177; --  APEX application ID
   v_session_id   NUMBER := 15685783007363; -- APEX application session ID
BEGIN
   SELECT MAX (workspace_id)
     INTO v_ws_id
     FROM apex_applications
    WHERE application_id = v_app_id;

   -- Set Workspace ID
   wwv_flow_api.set_security_group_id (v_ws_id);

   -- Set Application ID
   APEX_APPLICATION.g_flow_id := v_app_id;

   -- Set Session ID
   APEX_APPLICATION.g_instance := v_session_id;
END;


By executing the above PLSQL block we can simply query the APEX collections from PLSQL Developer/ SQL developer or Toad.

Steps to Implement

• Step 1: Create APEX Collection in APEX application

BEGIN
   apex_collection.create_or_truncate_collection (p_collection_name => 'TEST');

   apex_collection.add_member (p_collection_name   => 'TEST',
                               p_c001              => 'test row');
END;


• Step 2: Check from Toad or developer

Collection is not available.

• Step 3: Find out APP_ID and SESSION ID

• Step 4: Execute the above PLSQL BLOCK on your PLSQL Developer/ SQL developer or Toad with the above values
• Step 5: Run APEX_COLLECTIONS query

SELECT *
  FROM apex_collections
 WHERE collection_name = 'TEST';