The function user in oracle database

The other day I was reviewing an scenario where the customer was asking why the user "USER1" is not captured in a "after logon" trigger  in the database. When I took a look, I saw the following code:

 

declare

  v_login_user VARCHAR2(30) := NULL;

begin

  SELECT UPPER(sys.login_user) INTO v_login_user FROM dual;

  if (user in ('USER1','USER2','USER3') ) then

    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = AP1';

  end if;

exception

 when others then null;

END LOGIN_TRIGGER;

 

The customer created a session with "USER1" but the session didn't have the schema "AP1" set as default schema. 

The first impression was that they were not using the variable "v_login_user" where seems they are capturing the username of the session. So I started to investigate. 

It was so interesting for me when I saw that the PL/SQL works well, the "user" variable (I thought it was a variable) has the value "USER1". After some investigation I discovered that "user" is actually a public function in oracle that returns the name of the current user, that's why the PL/SQL was working. 

I confirmed this with the following piece of code:

BEGIN

     DBMS_OUTPUT.PUT_LINE('var user: '||user);

END;

/

var user: USER1

PL/SQL procedure successfully completed.

 

You can find more information about this function in the following links:

https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj42476.html

https://www.techonthenet.com/oracle/functions/user.php