RE: Logout system trigger

If the autonomous transaction doesn't work (from memory i think it won't but can't remember why) but you can submit a dbms_job which locks the account. Submitting a dmbs_job is dml so no problem there and then the job does the ddl.

I have used this approach for other ddl
a few times and it works fine. And yeah you can use dbms_scheduler if you prefer but I prefer dbms_job for one off jobs.

Regards,
Fergal
On 9 Oct 2014 21:02, <rajendra.pande_at_ubs.com> wrote:

> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jure Bratina
> *Sent:* Thursday, October 09, 2014 3:58 PM
> *To:* andrew.kerber_at_gmail.com
> *Cc:* mark.powell2_at_hp.com; 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: h <http://goog_1416375729>
> ttp://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_at_gmail.com>
> 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_at_hp.com> wrote:
> >
> > Shouldn't the command to lock the account just be: 'alter user my
> account lock' ?
> >
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] 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_at_gmail.com>
> 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?
> 11.2.0.4 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.
>

--
http://www.freelists.org/webpage/oracle-l