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;

• Execute the given cursor and return the number of rows processed
• (valid and meaningful only for INSERT, DELETE or UPDATE statements;
• for other types of statements, the return value is undefined and
• should be ignored).
• Input parameters:
• c
• Cursor id number of the cursor to execute.
• Return value:
• Number of rows processed if the statement in the cursor is
• either an INSERT, DELETE or UPDATE statement or undefined otherwise.

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