Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Delete Procedure Behaving Strangely

Re: Delete Procedure Behaving Strangely

From: Spencer <spencerp_at_swbell.net>
Date: Sun, 10 Dec 2000 22:15:45 -0600
Message-ID: <sxYY5.746$N3.133884@nnrp2.sbc.net>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US