Re: Delete Procedure Behaving Strangely

Hi,

The problem is that your 'in' variable has the same name as a column in your where-clause (since SQL is cause insencitive). The delete statement that you issued, deletes all rows where activeIngredientNo equal is to itself, so all rows.

Solution give your in variable an other name (eg: i_ActiveIngredietnNo).

I hope this will help.

Kris.

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.
Received on Mon Dec 04 2000 - 16:11:45 CST