使用 v('APP_USER') 作为 Oracle Apex 中列的默认值 [英] Use v('APP_USER') as default value for column in Oracle Apex

问题描述

我正在尝试使用 v('APP_USER') 作为列的默认值.当我在 select like

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')));

错误

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 ??

推荐答案

除了 V('APP_USER') 还有其他选项.从 Apex 5 开始，APP_USER 存储在 sys_context 中，这比 V() 函数的性能要高得多.它可以作为 SYS_CONTEXT('APEX$SESSION','APP_USER') 使用.

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.

话虽如此，审计列的最佳实践是填充 4 个审计列的触发器(如@Littlefoot 建议的那样).查看 quicksql(在 SQL Workshop > Utilities 下或在 livesql.oracle.com 上).如果您设置包含审计列"，您可以让它为您生成触发器.和Apex 启用".这种生成的触发器的一个例子是:

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;
/

这篇关于使用 v('APP_USER') 作为 Oracle Apex 中列的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！