Forum Stats

  • 3,839,663 Users
  • 2,262,524 Discussions
  • 7,901,034 Comments

Discussions

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

Sergio Vega
Sergio Vega Member Posts: 153 Red Ribbon
edited Oct 5, 2015 8:25AM in APEX Discussions

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.

2015-10-05_10-17-31.png

Does anyone know the reason of this?

Sergio

Tagged:

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,102 Red Diamond
    edited Oct 5, 2015 4:40AM
    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.
    
    2015-10-05_10-17-31.png

    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
    Hari_639 Member Posts: 1,484 Silver Trophy
    edited Oct 5, 2015 5:12AM

    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
    Sergio Vega Member Posts: 153 Red Ribbon
    edited Oct 5, 2015 5:28AM

    Thanks. I have modified the trigger but problem stills.

  • Sergio Vega
    Sergio Vega Member Posts: 153 Red Ribbon
    edited Oct 5, 2015 5:33AM

    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:

    2015-10-05_11-31-52.png

    Sergio

  • Hari_639
    Hari_639 Member Posts: 1,484 Silver Trophy
    edited Oct 5, 2015 8:17AM

    Hi Sergio,

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


    Regards,

    Hari

  • Sergio Vega
    Sergio Vega Member Posts: 153 Red Ribbon
    edited Oct 5, 2015 8:25AM

    Tried, but problem stills.

This discussion has been closed.