Forum Stats

  • 3,838,232 Users
  • 2,262,342 Discussions
  • 7,900,547 Comments

Discussions

Categories

can some one explain me this

Aravind Kumar Sekar
Aravind Kumar Sekar Member Posts: 27
edited Sep 16, 2015 5:36AM in SQL & PL/SQL

  :new.created_by := nvl(v('APP_USER'),user);

Tagged:
KayKAravind Kumar SekarChris HuntWilliam Robertson
· Share on TwitterShare on Facebook
«13

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Sep 16, 2015 1:59AM

    In the context of a trigger, set the new value of the created_by column to whatever the function returns. If the function returns null then take the current user.

    · Share on TwitterShare on Facebook
  • KayK
    KayK Member Posts: 1,723 Bronze Crown
    edited Sep 16, 2015 1:59AM

    Hi,

    do you have a little bit  more of your coding ?

    I expect it is from a trigger and it will assign a value to the created_by for new or updated rows in your table.

    v('xxx') may be a function to get a name of the user. If this function doesn't return a value different from NULL then the name of connected db-user is inserted.

    regards
    Kay

    · Share on TwitterShare on Facebook
  • Aravind Kumar Sekar
    Aravind Kumar Sekar Member Posts: 27
    edited Sep 16, 2015 2:05AM

    CREATE TABLE "ROLES"

       ( "ID" NUMBER(8,0) NOT NULL ENABLE,

       "ROLE_NAME" VARCHAR2(255) NOT NULL ENABLE,

       "CREATED_BY" VARCHAR2(255),

       "CREATED_ON" DATE,

       "UPDATED_BY" VARCHAR2(255) NOT NULL ENABLE,

       "UPDATED_ON" DATE NOT NULL ENABLE,

       CONSTRAINT "ROLES_PK" PRIMARY KEY ("ID")

      USING INDEX ENABLE

    )

    /

    CREATE OR REPLACE TRIGGER "BI_ROLES"

      before insert on "ROLES" 

      for each row 

    begin 

      if :NEW."ID" is null then

       select "ROLES_SEQ".nextval into :NEW."ID" from sys.dual;

      end if;

       if inserting then

       :new.created_on := sysdate;

       :new.created_by := nvl(v('APP_USER'),user);

       :new.updated_on := sysdate;

       :new.updated_by := nvl(v('APP_USER'),user);

       elsif updating then

       :new.updated_on := sysdate;

       :new.updated_by := nvl(v('APP_USER'),user);

       end if;

    end;

    · Share on TwitterShare on Facebook
  • Aravind Kumar Sekar
    Aravind Kumar Sekar Member Posts: 27
    edited Sep 16, 2015 2:07AM

    :new.created_by := nvl(v('APP_USER'),user);

    for what is the function of "v " in that line

    · Share on TwitterShare on Facebook
  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge
    edited Sep 16, 2015 2:09AM

    is there any stored function called v?

    · Share on TwitterShare on Facebook
  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Sep 16, 2015 2:10AM

    V is a function that someone has created.

    select text from all_source where name ='V' order by type, line;

    · Share on TwitterShare on Facebook
  • KayK
    KayK Member Posts: 1,723 Bronze Crown
    edited Sep 16, 2015 2:11AM

    It is a userd defined function.

    Do a "desc v"  to see the input  / output parameters

    Investigate the views dba_source / user_source to see the coding.

    · Share on TwitterShare on Facebook
  • PhilHerring
    PhilHerring Member Posts: 169
    edited Sep 16, 2015 2:17AM

    V() is the APEX function to get an application variable. In this case, it's getting the currently logged-on APEX user.

    KayKAravind Kumar Sekar
    · Share on TwitterShare on Facebook
  • Aravind Kumar Sekar
    Aravind Kumar Sekar Member Posts: 27
    edited Sep 16, 2015 2:20AM

    thanks for your answer i understand it ow very vell

    · Share on TwitterShare on Facebook
  • KayK
    KayK Member Posts: 1,723 Bronze Crown
    edited Sep 16, 2015 2:23AM

    will this APEX function work on a pure database trigger and will it be accessible ?

    I'm coming from Forms 4.5 (20 years ago) and have only a little knowledge of APEX.

    · Share on TwitterShare on Facebook
This discussion has been closed.