Re: What is wrong with this????

Vince wrote:
On Jan 30, 5:41 pm, Boogha <boo...@xxxxxxxxx> 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
.

Relevant Pages

  • Re: Bulk Collect without LIMIT
    ... Oracle, with 10g, merged the BULK COLLECT and FETCH to use the same mechanism. ... If you just say OPEN CURSOR ... ... for i in 1..500 loop ...
    (comp.databases.oracle.misc)
  • Re: Calling a SP inside a cursor loop..
    ... every loop iteration of the cursor. ... Fetch next From EffectiveDate_Cursor Into @FLD1,@FLD2 ... and If the Fetch stmt is below the begin Stmt, the loop iterations are ... the proper way to program a cursor loop is: ...
    (comp.databases.ms-sqlserver)
  • Re: -1555-ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" t
    ... it happen to fetch something from a big result set in a loop and commit ... Or does it happen that the query runs longer than ... Well, if this script receives ORA-1555 on regular basis, then you ...
    (comp.databases.oracle.server)
  • Re: Fetch out of sequence in cursor
    ... end loop; ... I have a cursor which loops through a table and does some processing. ... Towards the end of the loop I update another table and I want to COMMIT each ... I'm getting a fetch out of sequence message when i try to ...
    (comp.databases.oracle.misc)
  • Re: Where should I put the loops
    ... Thank you very much for the neat script. ... But I know if the page doesn't exit that Ruby will fetch the ... same papge again and again until it comes to the upper limit of loop. ... order to break out of the loop what I do is that I compare the website ...
    (comp.lang.ruby)