CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-apexoracle12c
Arif Sher Khan
Arif Sher Khan

Reputation: 585

Use v('APP_USER') as default value for column in Oracle Apex

I am trying to use v('APP_USER') as default value for a column. I get null when I use it in select like

select v('APP_USER') from dual;

But when I use it as default in column, like below, I am getting error.

create table test_table (col_1 varchar2(50) default NVL(v('APP_USER'), SYS_CONTEXT('USERENV','OS_USER')));

Error

create table test_table (col_1 varchar2(50) default NVL(v('APP_USER'), SYS_CONTEXT('USERENV','OS_USER')))
Error report -
ORA-04044: procedure, function, package, or type is not allowed here
04044. 00000 -  "procedure, function, package, or type is not allowed here"
*Cause:    A procedure, function, or package was specified in an
           inappropriate place in a statement.
*Action:   Make sure the name is correct or remove it.

Can anyone explain this or have a turnaround for this ??

Upvotes: 2

Views: 5237

Answers (2)

Koen Lostrie
Koen Lostrie

Reputation: 18665

There are other options than V('APP_USER'). Since Apex 5, the APP_USER is stored in the sys_context and that is a lot more performant than the V() function. It is available as SYS_CONTEXT('APEX$SESSION','APP_USER').

It also works as a default value for tables:

create table test_table
(col_1 VARCHAR2(100) DEFAULT SYS_CONTEXT('APEX$SESSION','APP_USER'));

Table TEST_TABLE created.

That being said, the best practice for audit columns is a trigger that populates the the 4 audit columns (as @Littlefoot suggested). Have a look at quicksql (under SQL Workshop > Utilities or on livesql.oracle.com). You can have it generate the triggers for you if you set "include Audit columns" and "Apex Enabled". An example of such a generated trigger is:

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

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 142753

One option is to use a database trigger, e.g.

CREATE OR REPLACE TRIGGER trg_biu_test
   BEFORE INSERT OR UPDATE ON test
   FOR EACH ROW
BEGIN
   :new.col1 := v ('APP_USER');
END;
/

Upvotes: 1

Related Questions