What is wrong with this????

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Boogha - 31 Jan 2008 01:41 GMT

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

Reply to this Message

Thomas Olszewicki - 31 Jan 2008 02:14 GMT

> I am trying to write a simple script to drop triggers for a user on a
> 10g database
[quoted text clipped - 27 lines]
> 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

Reply to this Message

Vince - 31 Jan 2008 02:15 GMT

> I am trying to write a simple script to drop triggers for a user on a
> 10g database
[quoted text clipped - 27 lines]
> Cheers,
> Adam

I tested your script and it worked as expected. Could you be using a
reserved word as a trigger name? Try adding an exception handler to
display v_dropstmt.

Reply to this Message

Serge Rielau - 31 Jan 2008 03:47 GMT

>> I am trying to write a simple script to drop triggers for a user on a
>> 10g database
[quoted text clipped - 31 lines]
> reserved word as a trigger name? Try adding an exception handler to
> display v_dropstmt.

..or a name with a space or other funnyness. This is safer:
'DROP TRIGGER "' || v_trgname || '"';

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Noons - 31 Jan 2008 02:21 GMT

> I am trying to write a simple script to drop triggers for a user on a
> 10g database
[quoted text clipped - 24 lines]
>
> Can anyone help, also the user has the permission to drop indexes

are u sure you not getting nulls into v_trgname
via the feed cursor?

Reply to this Message