APEX problem with custom authentication WWV_FLOW_DATA_SESSION_FK

Having battled to get Apex upgraded from 3.2 to 4.2 we seemed to have finally succeeded apart from one small issue - you couldn't login to any of the applications. Just a minor issue then....

I'm not that familiar with how APEX was set up and initially i just started googling the error we were getting back to see if there was anything obviously wrong







The error  ( ORA-02291 integrity constraint APEX_040200.WWV_FLOW_DATA_SESSION_FK violated )is just a parent keys not found type issue so it looked like something basic in the way the login information was being recorded.

My initial googling found an Apex bug so i took a look at that

http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-42-known-issues-1863578.html

Bug number 14784118.

This looked like a good hit until i found that it was supposedly fixed in our version anyway. However i thought I'd try and force the patch on anyway by bypassing the patch checks. This didn't fix it (at least oracle didnt reintroduce the bug...) so i was left stuck as there were very other good hits on google.

So i started to dig a little deeper into how the custom authentication was being done.

After much head scratching it seemed to be that the custom function was running a procedure that ran some java inside the database that logged on to the same database apex was running in using jdbc to authenticate! I still don't know why it was coded this way and maybe I'm still missing something....however i then created a new standard database user authorisation scheme and applied that to all the applications and now everything seems to work fine.

I think the problem is because the code that works fine in 3.2 is not doing some of the extra login initialisation that is required in 4.2. Specifically i think it's not creating rows in WWV_FLOW_SESSIONS (hence the PK violation). I think this is because of the lack of the call to the procedure wwv_flow_custom_auth_std.post_login which i think is responsible for some of this. I'm no expert though so i could be wrong.

Anyway hope this might be useful for someone else - had me scratching my head for a while.

Comments