Tony’s Oracle Tips

September 3, 2008

Change session id in APEX URL

Filed under: SQL Fun — tonyhasler @ 10:44 pm

Some of the more difficult questions to get answered are those that require expertise in more than one area.  I recently posted a question on OTN about forcing password changes during an Application Express login when a password expired.  I was given a couple of options but elected to redirect to the password change page using OWA_UTIL.REDIRECT_URL from a post-authentication routine in my authentication scheme.  However, the bit that didn’t get answered was how to change the session ID in the middle of the FSP_AFTER_LOGIN_URL to avoid the user having to login again after the password change. 

Providing you are using 10g you can use regular expressions to do this.  The following expression returns the correct result even when the URL (the bind variable FSP_AFTER_LOGIN_URL has colons replaced by vertical bars) is very short or non-existent.  You can use this in a page computation to set another page item and then use that item as the basis of a branch.  The code assumes that page 13 is the application home page.  Remember to change it if you cut and paste this code.

    WHEN :fsp_after_login_url IS NOT NULL
    THEN REGEXP_REPLACE (REPLACE (:fsp_after_login_url, '|', ':'),
                           '\1:\3:' || v ('SESSION') || ':\7'
     ELSE 'f?p=' || :app_id || ':13:' || v ('SESSION')

Let us work through this.   Firstly, we use standard replace to change the ‘|’ characters back to ‘:’s.  The first bracked section (group) in the pattern is just an arbitrary string.  The ‘?’ makes the string as short as possible.  However, the second group must be a colon or the end of the string so the first group picks up everything up to the first colon (or the whole line if there isn’t one).  The same logic means that the third group must be the page number and the fifth group the old session.  Note that if either or both of these strings are not supplied then the groups are null.  The seventh group is any remainder.

Because the pattern starts with ‘^’ (beginning of line) and ends with ‘$’ (end of line) the entire string is replaced.  The replacement string places the value of the APEX item SESSION into the URL in place of the fifth group.

Here are some example test results:

set echo off
set heading off
set feedback off

VARIABLE fsp_after_login_url varchar2(200);

EXEC :fsp_after_login_url := 'f?p=123|99|old_session|a|b|c';

          WHEN :fsp_after_login_url IS NOT NULL
             THEN REGEXP_REPLACE (REPLACE (:fsp_after_login_url, '|', ':'),
                                  '\1:\3:' || 'NEW_SESSION' || ':\7'

EXEC :fsp_after_login_url := 'f?p=123|99|old_session';


EXEC :fsp_after_login_url := 'f?p=123|99';

EXEC :fsp_after_login_url := 'f?p=123';


results are:






Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: