How to get the Oracle Apex userID or UserName that is logged in to write it to a text feild in a form

Question

I'm trying to get and write the userID that has updated a value in a form to a table.

So if I'm logged in as Bob then I would expect that the P3_USERID column to display Bob or my ID when the user selects Update.

Form

select * from SYSUSER where user_id = :APP_USER;```


[Error](https://i.sstatic.net/NnUxz.png)

Answer 1

Is it ok to assume that you are trying to capture auditing data. Who created/updated a row in this table and when did that happen. If that is the case then the best practice in apex is to have a trigger do that for you. Here is an example (generated with apex sql workshop > utilities > quicksql - settings include Audit columns):

-- create tables
create table mytable (
    id                             number generated by default on null as identity 
                                   constraint mytable_id_pk primary key,
    column1                        varchar2(100 char),
    created                        date not null,
    created_by                     varchar2(255 char) not null,
    updated                        date not null,
    updated_by                     varchar2(255 char) not null
)
;


-- triggers
create or replace trigger mytable_biu
    before insert or update 
    on mytable
    for each row
begin
    if inserting then
        :new.created := sysdate;
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end mytable_biu;
/

Then your apex form, do not include those page items on insert and set them read only on update.