Skip to Main Content
Feature Request FR-2154
Product Area Developer Experience
Status CLOSED

21 Voters

Cleanup PL/SQL code on session expire or logout

anton nielsen Public
· Nov 3 2021

Idea Summary
Allow developers to run pl/sql code as a session is being cleaned up by APEX (upon logout or session expire). For example, if I have a table that is similar to apex_collections or apex_application_temp_files, my_session_data, I would want to run
delete from my_session_data
where apex_session = :APP_SESSION;
at the same time that APEX deletes data from apex_collections and apex_application_temp_files.

Use Case
For example, if I have my own table (not a collection):

my_shopping_cart
  session_id	number,
  item_id		number,
  item_desc		varchar2(200)
  ...

I want to have a procedure

procedure my_cleanup(p_app_session_id   in number) is

begin
  delete from my_shopping_cart
  	where session_id = p_app_session_id;
end;

Preferred Solution (Optional)
Implement this either as an application process point or in similar fashion to similar to “Cleanup PL/SQL Code”, for example as “Logout / Session Expire PL/SQL Code.”

This is a great idea! You can already achieve this in APEX today with a slightly different approach.

Comments

Comments

  • mike_kutz OP 2.3 years ago

    I've seen this somewhere on someone else's presentation. (RAS Sessions aren't terminated on APEX Logout. This is how he was cleaning them up)

    Quick look, I only found such a option in Custom Authentication.  Haven't checked the Instance.

    But,  an option like this really does need to exist for the application level.

  • anton nielsen OP 2.3 years ago

    @mike_kutz I tested the Post Logout Procedure of custom authN schemes a while ago and, at least in that version, the procedure only ran if the user actively logged out. It did not run if the session just expired. I'll check again to be sure, but, as you said, this really should be available at the application level.

  • lorincz.zoltan OP 2.3 years ago

    End users very, very rarely click logout on websites. (me neither)
    Mostly they just close the browser or the browser tab.

  • vincent morneau Admin OP 2.3 years ago

    Hi @anton nielsen 

    We discussed this idea with the team and we think this is possible today with minimal effort:

    1. Create an automation that runs periodically (for example every 5min)
    2. Automation clears your shopping cart based on non-existant sessions
    begin
      delete from my_shopping_cart
      	where session_id not in ( select apex_session_id from apex_workspace_sessions );
    end;
    

    As other comments pointed out, users often don't logout and just exit the browser tab. This automation would work for any scenario.

    Hope that helps.

  • anton nielsen OP 2.3 years ago

    Thanks @vincent morneau , the automation is a good approach. Thanks again.