Re: What is wrong with this????
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