Re: What is wrong with this????

From: Thomas Olszewicki <ThomasO_at_cpas.com>
Date: Wed, 30 Jan 2008 18:14:53 -0800 (PST)
Message-ID: <2ef4f721-674c-461e-a23e-1d105a2511d2@j78g2000hsd.googlegroups.com>


On Jan 30, 8:41 pm, Boogha <boo..._at_gmail.com> wrote:
> I am trying to write a simple script to drop triggers for a user on a
> 10g database
>
> declare
>     v_trgname   varchar2(30);
>     v_dropstmt  varchar2(60);
>     cursor c_triggers is
>         select trigger_name from user_triggers
>         where trigger_name not like 'TRG_%';
> begin
>     open c_triggers;
>     loop
>     fetch c_triggers into v_trgname;
>     exit when c_triggers%notfound;
>         v_dropstmt := 'DROP TRIGGER ' || v_trgname;
>         execute immediate v_dropstmt;
>     end loop;
>     close c_triggers;
> end;
>
> As you can see there is no rocket science in the script. When I run
> the script I get the following error:
>
> Error at line 4
> ORA-00933: SQL command not properly ended
> ORA-06512: at line 13
>
> Can anyone help, also the user has the permission to drop indexes
>
> Cheers,
> Adam

Runs fine for me:
SQL> declare

  2      v_trgname   varchar2(30);
  3      v_dropstmt  varchar2(60);
  4      cursor c_triggers is
  5          select trigger_name from user_triggers
  6          where trigger_name not like 'TRG_%';
  7  begin
  8      open c_triggers;
  9      loop
 10      fetch c_triggers into v_trgname;
 11      exit when c_triggers%notfound;
 12          v_dropstmt := 'DROP TRIGGER ' || v_trgname;
 13          execute immediate v_dropstmt;
 14      end loop;
 15      close c_triggers;

 16 end;
 17 /

PL/SQL procedure successfully completed

HTH
Thomas Received on Wed Jan 30 2008 - 20:14:53 CST

Original text of this message