Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: trigger Problem

Re: trigger Problem

From: Glen Upreti <Glen.Upreti_at_nau.edu>
Date: Thu, 08 Jul 1999 09:27:19 -0700
Message-ID: <3784D167.2A985B5C@nau.edu>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US