Pages

Wednesday, March 5, 2014

Referencing USER in APEX


It’s not uncommon to reference the current user as USER in your pl/sql code. A simple use case may be to determine the client or environment that you’re running in (ex: dev, test, prod).

Referencing USER will have some slight side effects when running the code in APEX as the current USER is actually APEX_PUBLIC_USER (or what ever user you configured). This can cause issues in your application. To resolve it, simply reference sys_context('userenv','current_schema’) instead.

Example:
-- Via SQL*Plus
select user, sys_context('userenv','current_schema')
from dual;

USER        SYS_CONTEXT('USERENV
---------- --------------------
GIFFY        GIFFY
If you run the same query in APEX the output is shown below. You'll notice that referencing USER it does not display my current schema (GIFFY in this case).
The same applies to compiled code executed from APEX. For example if you have a procedure that references USER and that procedure is run from APEX then USER will be APEX_PUBLIC_USER.

This can be really tough to detect in automated tests as when testing via SQL*Plus, USER will return the current schema name.

2 comments:

  1. When I try 'SESSION_USER' in APEX (in SQL Workshop), I get 'APEX_PUBLIC_USER'

    I have to use - SYS_CONTEXT('USERENV','CURRENT_USER') - to get the parsing schema

    ReplyDelete
    Replies
    1. Thanks for pointing this out. I had left in the SESSION_USER as a typo from a previous test I was doing. I've updated the sample code.

      Delete