For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
:new.created_by := nvl(v('APP_USER'),user);
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.
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.
regardsKay
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.updated_on := sysdate;
:new.updated_by := nvl(v('APP_USER'),user);
elsif updating then
end;
for what is the function of "v " in that line
is there any stored function called v?
V is a function that someone has created.
select text from all_source where name ='V' order by type, line;
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.
V() is the APEX function to get an application variable. In this case, it's getting the currently logged-on APEX user.
thanks for your answer i understand it ow very vell
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.
i used different alphabet but its showing error..
can we use any other alphabet other than v ?
That's what i fear, the database doesn't know this APEX function or even your user hasn't the rights to use this function.
Try this
SELECT * FROM all_objects -- or dba_objects if you have access to this WHERE object_name = 'V';
SELECT *
FROM all_objects -- or dba_objects if you have access to this
WHERE object_name = 'V';
If you don't have access to v then you can try to work without it:
:new.created_by := user;
That works without APEX.
:new.created_by := nvl(t('APP_USER'),user);
here i used "t" but its showing error as
Compilation failed, line 7 (12:33:48) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PLS-00201: identifier 'T' must be declaredCompilation failed, line 7 (12:33:48) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.
PL/SQL: Statement ignored
your database user who create your trigger doesn't see the function v or t or he isn't allowed to execute it.
Have a look at all_objects.
Amazing. You put random letters into your code, and Oracle doesn't automatically translate them into functions which do whatever it is you want them to do. Who would have guessed that?
Where did 't' come from? Error says there is no such thing as 't'.
What was wrong with v? What errors were you getting? What do you even want this trigger to achieve? And if you're feeling super helpful, what version of Oracle is this? What version of apex are you using?
KayK wrote: 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.
KayK wrote:
If the trigger or procedure is called from within an Apex application session, then yes the v functions will work just fine. If they're called from a regular session then they will return null.
2848515 wrote: i used different alphabet but its showing error.. can we use any other alphabet other than v ?
2848515 wrote:
No. the "v" functions are specific to Apex application environment. You cannot just randomly pick any letter and expect it to work, unless you've specifically created functions (or have access to functions) with that name.
i just tried with other letters will they work or not ?
that's it...
No, random letters will not work unless you have a function named by that random letter.
OK, I am new to pl/SQL, apex so I am trying to do simple program
Thanks you somuch
The point is that this function is not needed in the database. You can simply replace it with :new:created := user; as Kim mentioned.
Alternatively if you do not want or can not change the code you can create a subsititute function for v on the database like this
create or replace function v (p_name varchar2)
IS
BEGIN
RETURN user;
END;
it's called Oracle - it knows everything ;-)
Lothar Flatz wrote: The point is that this function is not needed in the database. You can simply replace it with :new:created := user; as Kim mentioned. Alternatively if you do not want or can not change the code you can create a subsititute function for v on the database like this create or replace function v (p_name varchar2) IS BEGIN RETURN user; END;
Lothar Flatz wrote:
Really?
User is the database user.
v('APP_USER') in apex provides the application user, which may have been authenticated through Active Directory or some other authentication scheme, and is most certainly not likely to be a database user.
Before making changes, it's important to know what the intention and requirement is.