How do I run procedure x after the user has logged in?This is often required for such tasks as determining user access, such as populating a restricted Application Item relating to a role like F_IS_ADMIN based on the username defined in substitition string APP_USER
Those new to APEX and unfamiliar with certain concepts may consider using an Application Computation, firing "On New Instance (new session)"
|Application Computation Frequency|
It sounds fair enough, and I remember doing the same thing when I was first learning APEX. The documentation on understanding page computations states
The computation point On New Instance executes the computation when a new session (or instance) is generatedThis still isn't clear, but this actually fires when you first navigate to a page in your application - any page. This means when you first open a page like /apex/f?p=100:1, which may redirect you to the login page - the 'on new instance' event has already fired since APEX needed to provide you with a new session to render the home/login page.
In other words, these events can be generally described in the following order:
- Open home page
- On New Instance (new session) - APP_USER is 'nobody'
- Redirect to login page
- Validate credentials - APP_USER now set
- Redirect to relevant page
I think I've only used 'On New Instance' once or twice, possibly to prime content of application items - but I use 'Post-Authentication' all the time to calculate values based on the user who just logged in.
Post-Authentication is defined in the Authentication Scheme, and expects the name of a stored procedure.
|Shared Components -> Authentication Scheme|
PROCEDURE post_authentication IS
-- do stuff here
For better performance and code management you should place it within a PL/SQL package. That way it doesn't need to be interpreted dynamically every time a user logs in.
The Post-Authentication Procedure Name attribute just needs the name of the stored procedure, no semi-colon.
The PL/SQL may run something like:
IF v('APP_USER') = 'WESLEYS' THEN apex_util.set_session_state('F_IS_ADMIN','Y'); END IF;I understand changes are coming in APEX 5 regarding when these events fire as you navigate between applications that share authentication.
This also reminds me of a little experiment I wrote ages ago to determine the order of page/application events.