Re: What is wrong with this????

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Wed, 30 Jan 2008 22:47:24 -0500
Message-ID: <60cumhF1psjspU2@mid.individual.net>

Vince wrote:

> On Jan 30, 5: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

> 
> 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.

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

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Received on Wed Jan 30 2008 - 21:47:24 CST