Apex 4.2 and Database trigger issue:APEX_PUBLIC_USER instead of APEX user

Sergio Vega wrote:

I have an application in Apex 4.2 that contains a page with a form to submit data to a table. This table has a trigger "BEFORE INSERT OR UPDATE ON MY_TABLE FOR EACH ROW" used to get user and date.
Now, for each record I add using that page and form the trigger is saving into :NEW.user_ins this value "APEX_PUBLIC_USER".

  IF INSERTING THEN
    :NEW.dt_ins   := SYSDATE;
    IF :NEW.user_ins IS NULL THEN
     :NEW.user_ins := nvl(wwv_flow.g_user,user);
    END IF;
  END IF;

Page is not public so I think there's not reason to use that value.

Does anyone know the reason of this?

I would assume that it is due to the use of the undocumented and unsupported expression wwv_flow.g_user in the trigger. For whatever reason—undocumented features are subject to change or removal without notice—the value of this expression is presumably null at the time it is evaluated, leading to the NVL returning the database USER instead. Use the documented APP_USER built-in (and COALESCE instead of NVL for a tiny performance improvement):

  IF INSERTING THEN
    :NEW.dt_ins  := SYSDATE;
    IF :NEW.user_ins IS NULL THEN
    :NEW.user_ins := coalesce(v('APP_USER'), user);
    END IF;
  END IF;

Hi Sergio,

Can you try v('APP_USER') option (as pointed by fac586)

Regards,

Hari