Nice!! Maybe an autonomous transaction? Regards - Raj Pande UBS AG Platform Services - Operations Global Service Delivery (GSDM) 480 Washington Blvd. Jersey City, NJ 07310 TEL# - External - +1 201 318 7597 Internal - 19 436 7597 From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jure Bratina Sent: Thursday, October 09, 2014 3:58 PM To: andrew.kerber@xxxxxxxxx Cc: mark.powell2@xxxxxx; Oracle-L Freelists Subject: Re: Logout system trigger Hi, when tracing the session when such a trigger fires, an ORA-30511 is found in the trace: PARSING IN CURSOR #10476772 len=76 dep=1 uid=553 oct=47 lid=553 tim=1412883002571503 hv=1931945942 ad='45bd7ad0' sqlid='11j0qr5tkf9yq' begin execute immediate 'alter user u1 identified by a account lock'; end; END OF STMT PARSE #10476772:c=2999,e=3411,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1412883002571501 ===================== PARSING IN CURSOR #10473708 len=41 dep=2 uid=553 oct=43 lid=553 tim=1412883002593638 hv=1975896108 ad='4752a160' sqlid='ca8n0fxuwbk1c' alter user u1 identified END OF STMT PARSE #10473708:c=0,e=21909,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=1412883002593636 CLOSE #10473708:c=0,e=5,dep=2,type=0,tim=1412883002593965 EXEC #10476772:c=0,e=22382,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=1412883002594025 ERROR #10476772:err=30511 tim=1412883002594048 $ oerr ora 30511 30511, 00000, "invalid DDL operation in system triggers" // *Cause: An attempt was made to perform an invalid DDL operation // in a system trigger. Most DDL operations currently are not // supported in system triggers. The only currently supported DDL // operations are table operations and ALTER?COMPILE operations. // *Action: Remove invalid DDL operations in system triggers. Might be because of this restriction: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS797 Restrictions (for BEFORE LOGOFF triggers): DDL on other objects is limited to compiling an object, creating a trigger, and creating, altering, and dropping a table. Regards, Jure Bratina On Thu, Oct 9, 2014 at 9:41 PM, Andrew Kerber <andrew.kerber@xxxxxxxxx> wrote: The syntax is valid. I was just adding an extra level of complexity by changing the password again. Sent from my iPad > On Oct 9, 2014, at 2:02 PM, Powell, Mark <mark.powell2@xxxxxx> wrote: > > Shouldn't the command to lock the account just be: 'alter user my account > lock' ? > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On > Behalf Of Andrew Kerber > Sent: Thursday, October 09, 2014 12:14 PM > To: Oracle-L Freelists > Subject: Re: Logout system trigger > > Here is the trigger so far: > > create or replace trigger my_logoff_trigger before logoff on my.schema declare > sql_cmd varchar2(200); > pwd varchar2(30); > begin > dbms_output.enable(10000); > update my_audit > set logoff_time=systimestamp > where os_pid=(SELECT P.SPID FROM V$PROCESS P INNER JOIN V$SESSION S ON > S.PADDR = P.ADDR WHERE S.AUDSID = sys_context('USERENV', > 'SESSIONID')) > and oracle_session_id=SYS_CONTEXT('USERENV','SESSIONID'); > commit; > pwd:=generate_password(); > sql_cmd:='alter user my identified by '||pwd||' account lock'; > execute immediate sql_cmd; > end if; > exception > when others then > RAISE; > end; > > Sent from my iPad > >> On Oct 9, 2014, at 11:00 AM, Andrew Kerber <andrew.kerber@xxxxxxxxx> wrote: >> >> I am trying to create a trigger that locks an account after a user >> disconnects. It also writes a record to an auditing table. It writes the >> record successfully, then I use execute immediate to lock the account, but >> the lock command seems to be ignored. I expect there is some special >> processing I need to do. Does anyone have an example of how to do this? >> 184.108.40.206 EE on Linux. >> >> Sent from my iPad > -- > http://www.freelists.org/webpage/oracle-l > > > -- > http://www.freelists.org/webpage/oracle-l > > -- http://www.freelists.org/webpage/oracle-l
Please visit our website at http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html for important disclosures and information about our e-mail policies. For your protection, please do not transmit orders or instructions by e-mail or include account numbers, Social Security numbers, credit card numbers, passwords, or other personal information.