Re: Delete Procedure Behaving Strangely

Your bind variable (equals your procedure parameter) has the same name as the column name in the table.
You should never do that as PL/SQL can't cope with that. Prefix the parameter with a p_ (convention) and you should be fine.

Regards,

Sybrand Bakker, Oracle DBA

<seniag75_at_my-deja.com> wrote in message news:90h2ia$ld2$1_at_nnrp1.deja.com...
> 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 - 15:46:52 CST