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';