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

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

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

   -- Refresh Item Session State Values

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 and Oracle XE database.

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