APEXblog.nl - Tips and Tricks

About This Blog:
I (Richard Weug) started this blog primary for myself to save all kinds of Apex tips and tricks in one place. To use it as a place to write down how I used some coding in my own projects, but also to copy and paste all kinds of articles I find on the Internet. (So I never have to wonder on what website did I read about??? When I see something interesting I collect the content so I have my own archive/knowlegde base.

View my profile on LinkedIn

 

Tips & Tricks

Print
05
Mar

Referencing USER in APEX

Written by Richard Weug. Posted in Tips and Tricks

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.

original article written by: Martin Giffy D'Souza