Delete Procedure Behaving Strangely

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);

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:32:58 CST