Home » SQL & PL/SQL » SQL & PL/SQL » USERENV, and OS_USER
USERENV, and OS_USER [message #670171] Wed, 13 June 2018 10:26 Go to next message
desmond30
Messages: 29
Registered: November 2009
Junior Member
My goal is to store the os user of the person who is inserting into the table


create table mytable(myid number, create_user varchar2(40))




CREATE OR REPLACE TRIGGER trig_mytab
BEFORE INSERT OR UPDATE
ON mytable
FOR EACH ROW
DECLARE
v_user   varchar2(30);
BEGIN

select substr(sys_context( 'USERENV', 'OS_USER' ),1,25) 
INTO v_user
from dual;



END;

insert into mytable (myid) values(1);

commit;





when I select * from mytable, why do I see a blank in create_user ?
Re: USERENV, and OS_USER [message #670172 is a reply to message #670171] Wed, 13 June 2018 10:40 Go to previous messageGo to next message
cookiemonster
Messages: 13134
Registered: September 2008
Location: Rainy Manchester
Senior Member
Because the trigger doesn't set create_user.
It sticks the value in a local variable v_user and then does nothing with it.
Oracle isn't going to modify the contents of a column unless you actually tell it to.
Re: USERENV, and OS_USER [message #670173 is a reply to message #670172] Wed, 13 June 2018 10:51 Go to previous messageGo to next message
desmond30
Messages: 29
Registered: November 2009
Junior Member
oops my bad, yes it works now, I forgot to assign the variable to my create_user column
Re: USERENV, and OS_USER [message #670174 is a reply to message #670173] Wed, 13 June 2018 11:09 Go to previous message
Solomon Yakobson
Messages: 2756
Registered: January 2010
Location: Connecticut, USA
Senior Member
Very inefficient code:

CREATE OR REPLACE
  TRIGGER biur_mytab
  BEFORE INSERT
      OR UPDATE
  ON mytable
  FOR EACH ROW
  BEGIN
      :new.create_user := substr(sys_context('USERENV','OS_USER'),1,25);
END;
/

SY.
Previous Topic: How to restrict IN parameter length in Procedure
Next Topic: How To display last day of a century
Goto Forum:
  


Current Time: Sun Jun 24 16:22:57 CDT 2018