View Item Session State from PL/SQL IDE

One and a half year ago I had a post about viewing data from APEX collections from your favorite PL/SQL IDE (PL/SQL Developer, TOAD, SQL Developer...). Similar to that you can view current item session state. First step should be getting grants from some internal APEX packages (executing as sys user):

grant execute on apex_040200.wwv_flow_security to test;
grant execute on apex_040200.wwv_flow_session_state to test;

After that you have to register application and session ID in your PL/SQL IDE (running in parsing schema, e.g. test):

begin
   -- Set Application ID       
   apex_040200.wwv_flow_security.g_flow_id := 121;
      
   -- Set Session ID  
   apex_040200.wwv_flow_security.g_instance := 802090201361;   
end; 

Any time you want to see current session state from some item you have to refresh current session state values by executing:

begin   
   -- Refresh Item Session State Values
   apex_040200.wwv_flow_session_state.fetch_into_substitution_cache(true);
end;

After that you can see value from any item in current session using function v or apex_util.get_session_state, for example:

select v('P1_TEST') from dual

Tested with APEX 4.2.4.00.08 and Oracle XE 11.2.0.2.0 database.

NOTE: It wouldn't be a good idea to give these grants on production instances. :)

View data from APEX collections in PL/SQL IDE

If you ever wondered, there's an easy way to see data from APEX collections in your favorite PL/SQL IDE (PL/SQL Developer, TOAD, SQL Developer...).

You only have to run short anonymous PL/SQL block and define:

• workspace name (workspace ID)
• application ID
• session ID.

Here's the script:

declare

  v_workspace_id apex_workspaces.workspace_id%type;

begin

  select workspace_id

    into v_workspace_id

    from apex_workspaces

   where workspace = '&WORKSPACE_NAME';

   

   -- Set Workspace ID

   apex_util.set_security_group_id(v_workspace_id);

   -- Set Application ID

   apex_application.g_flow_id  := &APP_ID;     

   

   -- Set Session ID

   apex_application.g_instance := &APP_SESSION;  

end;

After you run this script you can easily do query from apex_collections view and you'll see the result.