APEX 18.1: Debugging of the APEX sessions was never easier

I've already blogged about apex_session package and the debugging of the APEX sessions in version 5.1. Same package got bigger in 18.1 with 4 new great procedures:

• CREATE_SESSION
• DELETE_SESSION
• ATTACH
• DETACH

Those procedures made debugging of the APEX sessions even easier. The same was possible before by using OraOpenSource's OOS_UTIL_APEX package, but it's somehow nicer when you can use native functionality.

With this procedures you can create or join existing session from your favorite IDE and use/debug all those session dependent features like:

• collections
• item session state
• apex_mail
• ...

To use any of the procedures you have to run them as application parsing schema, one of schemas assigned to the workspace or one of the users with APEX_ADMINISTRATOR_ROLE role.

To create session run following command:

begin
  apex_session.create_session(100,1,'MGORICKI');
end;
/

After that, if you run following SQL statement:

select v('APP_ID') as app_id
     , v('APP_SESSION') as app_session
     , v('APP_USER') as app_user 
  from dual;

you'll see that all necessary APEX variables are set:

MGORICKI@db12c.local> select v('APP_ID') as app_id, v('APP_SESSION') as app_Session, v('APP_USER') as app_user from dual;
APP_ID  APP_SESSION  APP_USER  
                               


MGORICKI@db12c.local> exec apex_session.create_session(100,1,'MGORICKI');

PL/SQL procedure successfully completed.

MGORICKI@db12c.local> select v('APP_ID') as app_id, v('APP_SESSION') as app_Session, v('APP_USER') as app_user from dual;
APP_ID  APP_SESSION    APP_USER  
100     2847775298572  MGORICKI

It's similar if you want to join existing session. You just have to copy session ID (3rd parameter from APEX URL) of the runtime app and add it to the apex_session.attach call:

begin
  apex_session.attach (
    p_app_id     => 100,
    p_page_id    => 1,
    p_session_id => 837851762118
  );
end;
/

After that you can get/set item session state, query/create/modify collection and a lot more...

Set/Get Item Session State

Lets say you have v function call inside of a view or a procedure (I don't encourage you to do that, it's better to use bind variables or input parameters). Now, you can easily debug it from IDE by setting item session state:

MGORICKI@db12c.local> create or replace view v_emp_filtered 
  2  as 
  3    select * 
  4      from emp 
  5     where deptno = v('P1_DEPTNO');

View V_EMP_FILTERED created.

MGORICKI@db12c.local> 
MGORICKI@db12c.local> select * from v_emp_filtered;

no rows selected
MGORICKI@db12c.local> exec apex_util.set_session_state('P1_DEPTNO', 10);

PL/SQL procedure successfully completed.


MGORICKI@db12c.local> select * from v_emp_filtered;
EMPNO  ENAME   JOB        MGR   HIREDATE   SAL   COMM  DEPTNO  
7839   KING    PRESIDENT        17-NOV-81  5000  10    10      
7782   CLARK   MANAGER    7839  09-JUN-81  2450        10      
7934   MILLER  CLERK      7782  23-JAN-82  1300        10      

Create/Query Collections

Same goes for the collections. You can now query/create/modify them from IDE:

MGORICKI@db12c.local> select seq_id, c001 as empno, c002 as ename, c006 as sal from apex_collections where collection_name = 'EMP';

no rows selected

MGORICKI@db12c.local> exec apex_collection.create_collection_from_query('EMP', 'select * from emp');
PL/SQL procedure successfully completed.


MGORICKI@db12c.local> select seq_id, c001 as empno, c002 as ename, c006 as sal from apex_collections where collection_name = 'EMP';
SEQ_ID  EMPNO  ENAME   SAL   
1       7839   KING    5000  
2       7698   BLAKE   2850  
3       7782   CLARK   2450  
4       7566   JONES   2975  
5       7788   SCOTT   3000  
6       7902   FORD    3000  
7       7369   SMITH   800   
8       7499   ALLEN   1600  
9       7521   WARD    1250  
10      7654   MARTIN  1250  
11      7844   TURNER  1500  
12      7876   ADAMS   1100  
13      7900   JAMES   950   
14      7934   MILLER  1300  


14 rows selected.

To reset your IDE environment use delete or detach procedures.

You can find more about the apex_session package in documentation.

Enjoy!