Data clean up operations

[drkestrel]

I have a procedure of the following format

PROCEDURE proc_UnWantedRIC IS
BEGIN
  DELETE FROM MyTable where field1='SomeCondition';
  UPDATE MyTable set field2='Whatever2' WHEN field2='Somethingelse2';
  UPDATE MyTable set field3='Whatever3' WHEN field3='Somethingelse3';
END;

The first delete line get executed, but the second and third line did't? Anything special?

 

[karluk]

Is that the exact text of your procedure? If so you have a syntax error. The WHEN clause isn't valid in an update statement. It should be WHERE instead. (My sympathies. You are probably still thinking about your SQL*Loader problem.)

 

[drkestrel]

Karluk,
It's just my typo. The WHEN should read WHERE.

But still befuddled why only the first SQL statement work but the second statement onwards doesn't!
Stangely, in an attempt to debug, I put

dbms_output.put_line("debug statement")

before and after each staement, and not even the first debug code (before the DELETE staement) get printed despite the fact that the delete is successful.........wassup?

 

[MikeJones]

Try putting a

DBMS_OUTPUT.PUT_LINE('Statement processed '|| to_char(SQL%ROWCOUNT) || ' Rows');

after each DML statement, this will show you how many rows it processed (I'd expect to see 0)

Also make sure you have "set serveroutput on" in the SQL Plus session you are running this from.