Re: Delete Procedure Behaving Strangely

In our last gripping episode seniag75_at_my-deja.com wrote:
> Hello:
>
> I have written the following stored procedure below....
>
> CREATE OR REPLACE PROCEDURE "USP_ACTIVEINGREDIENTS_DELETE"
> (
> ActiveIngredientNo IN tblActiveIngredients.ActiveIngredientNo%TYPE,
> retval OUT NUMBER
> )
> AS
>
> BEGIN
> DELETE FROM
> tblActiveIngredients WHERE "ACTIVEINGREDIENTNO" =
 ActiveIngredientNo;
>
> retval := SQL%ROWCOUNT;
>
> COMMIT;
>
> END;
>
> When I run the procedure in SQL PLUS using the following code.
>
> SET SERVEROUTPUT ON;
> SET AUTOCOMMIT ON;
> DECLARE
> P9 NUMBER;
> BEGIN
> usp_ActiveIngredients_Delete(<any number>, P9);
> DBMS_OUTPUT.PUT_LINE(P9);
> END;
> /
>
> The procedure executes and deletes ALL rows in my table, not just the
> one with the key I have specified. I am utterly confused, as this
> makes no sense. If I type the SQL directly into SQL Plus "Delete From
> tblActiveIngredients Where ActiveIngredientNo = <AnyNumber>" it works
> fine.
>
> Can anyone tell me what I am doing wrong?
>
> Thanks.
>
> JG
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

You've named the input variable the same as the column name, therefore the procedure is deleting every record in the table. It doesn't matter that you've specified the column in all-capitals and enclosed the name in quotation marks, the names are the same since, with DML statements, oracle is case-insensitive. Change the input variable name in the procedure and it should work as expected.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.