Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: trigger Problem
Thanks for yoru response Mark, I looked at all my other stored procs
that use dbms_sql and I don't use execute in any of them! and they all
work!
I looked up the comments on dbms_sql.execute and it says: function execute(c in integer) return integer;
wouldn't this not apply, since a. I am not inserting updating or deleting, and b. I don't need the cursor re-executed?
I put the sys.dbms_sql.execute(cnum); where you recommended, but now I
get the following error:
PLS-00221: 'EXECUTE' is not a procedure or is undefined
what's up?
Again, thanks for your response
enjoy,
--glen
Mark Styles wrote:
>
> Glen Upreti <Glen.Upreti_at_nau.edu> instructed their monkeys to type:
>
> >CREATE OR REPLACE TRIGGER LOCKER
> > AFTER INSERT ON BOB.SEC_TABLE
> >FOR EACH ROW
> >DECLARE
> >cnum INTEGER;
> >
> >BEGIN
> > cnum := SYS.DBMS_SQL.OPEN_CURSOR;
> > SYS.DBMS_SQL.PARSE(cnum,'ALTER USER '||:NEW.USERID||' ACCOUNT
> >LOCK',SYS.DBMS_SQL.NATIVE);
>
> looks to me like you need a 'dbms_sql.execute(cnum);' here
>
> > SYS.DBMS_SQL.CLOSE_CURSOR(cnum);
>
> >I also wrote a stored proc that takes an in
> >of userid and locks the users account (this works by itself), but when
> >it is called by the trigger it fails to lock the user's account!
>
> I don't know why this is though
>
> >PS what does HTH stand for?
>
> Hope That Helps
>
> HTH :)
>
> Mark Styles
> Oracle developer and DBA
> http://www.lambic.co.uk/company
--
|-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-|
Glen Upreti
Senior Oracle DBA
Northern Arizona University
Phone (520)523-8393 Fax(520)523-7407
Glen.Upreti_at_NOSPAMnau.edu
Received on Thu Jul 08 1999 - 11:27:19 CDT
![]() |
![]() |