delete from deleting complete table in pl/sql procedure: ORACLE

Question

I need to delete selective rows from Database using pl/sql procedure. But it deletes all rows irrespective of what is in where clause.

  The student table is:-
  id    name        class
  1    danial        9
  2    patrick       9
  3    paul          8

             create or replace procedure delete_student (stId in NUMBER)
             begin
                  delete from students where class = stId;
                  commit;
             end;

I call this procedure from jsf application sending, say 9 as argument. However, this procedure in effect deletes all three rows in the student table irrespective of the value of argument stId which outputs correctly in the DBMS_OUT console. What am i doing wrong.

Are you sure you are passing 9. Did you try to log the passed value somewhere? — Hawk
yes sir. 100%. it print 9 when i send 9 and 8 when sent 8. but delete all three rows whether its 8 or 9.i even hard with in procedure and it would still delete all rows. — jaykio77
This is interesting, I failed to replicate your case. Unless there are other statements are being executed (other than what you showed us), it should not delete all rows. I would also check if there is any trigger on the table, or wait for more expert inputs — Hawk
yes sir indeed it is. i can upload pictures step by step. its really weird behavior. there is no trigger. inside pl/sql i wrote, 'delete from students where class = 8'; and ran the procedure it delete all three rows. — jaykio77
The example you're showing here works correctly. So the problem is in somewhere else. — user272735

Gary Myers Gary Myers · Accepted Answer · 2017-03-01T02:18:58

Does the name of the parameter coincide with the name of a column in the table ?

It is good practice to name parameters and variables with a prefix (such as p_, v_) so that SQL statements are unambiguous when differentiating between column names and variable / parameter names.

delete from deleting complete table in pl/sql procedure: ORACLE

1 Answers