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

Sergio Vega

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?

Sergio

fac586

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".

1.   IF INSERTING THEN 
2.     :NEW.dt_ins  := SYSDATE; 
3.     IF :NEW.user_ins IS NULL THEN 
4.     :NEW.user_ins := nvl(wwv_flow.g_user,user); 
5.     END IF; 
6.   END IF; 

  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;

Hari_639

Hello,

Documented alternative for wwv_flow.g_user is APEX_APPLICATION.G_USER.

to me, issue looks like something else. What's your authentication scheme? How are you submitting the form? Is this ARP form or you have written custom pl/sql to insert data?

Please provide more info.

Regards,

Hari

Sergio Vega

Thanks. I have modified the trigger but problem stills.

Sergio Vega

Hi Hari,

I I'm using a page created with the wizard (automatic row fetch, automatic row processing) without including column USER_INS so the trigger will fill it.

Security attributes of the application are:

Sergio

Hari_639

Hi Sergio,

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

Regards,

Hari

Sergio Vega

Tried, but problem stills.