Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Delete Procedure Behaving Strangely
the name of your pl/sql variable is identical to the name of a column in the table. oracle is interpretting the name as a column name, not a pl/sql variable. naming conventions for pl/sql variables can help you avoid this type of problem.
<seniag75_at_my-deja.com> wrote in message news:90h2hl$lcp$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 Sun Dec 10 2000 - 22:15:45 CST