Tuesday, 8 November 2016

APEX User details

NVL(v('APP_USER'), user) alternative

Fresh from the factory - AUSOUG Connect2016

One of the things that I will take home from AUSOUG Connect2016 in Perth is nice little tip from Scott and his team

If you ever wanted to pick up on a user identity using the application within your triggers most likely you opted for:
NVL(v('APP_USER'), user) 

For the future you might be using:
coalesce ( sys_context('APEX$SESSION', 'app_user')
         , regexp_substr(sys_context('userenv',   'client_identifier') ,'^[^:]*') --for remote users
         , sys_context('userenv', 'session_user')

Reason being it works faster then NVL combination especially on large scale data. 

When working in Oracle 12c this can even became part of you table script:

, created_by varchar2(40) default
  coalesce ( sys_context('APEX$SESSION', 'app_user')
         , regexp_substr(sys_context('userenv',   'client_identifier') ,'^[^:]*')
         , sys_context('userenv', 'session_user')
 not null
So there will be no need to do this in your trigger body anymore.

Thanks again for sharing.


No comments:

Post a Comment