Sunday, March 9, 2014

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. :)