Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bad bind variable & best practice for delete

771462May 6 2010 — edited May 6 2010
I am working with three tables and very new to SQL. I need to create a procedure that will accept an ID and go through two sub tables and delete child records. Item is the main table. I am passing in the ID into the procedure and I want to use it as below. I keep getting a bad bind variable error message. I have verified that the table is setup as a number and my procedure accepts a number. I also want someone to review this from best practice as I am new to procedures.

PROCEDURE DeleteItem (p_ItemID IN NUMBER, p_RowsAffected OUT number)
IS
p_RowsAffected NUMBER;
-- select the itemdetail for the analysis
CURSOR c_itemdetail
IS
SELECT
itemdetailid
FROM itemDETAIL
WHERE itemid = :p_ItemID;
BEGIN

-- loop through each itemdetail and delete the itemdetailoutlay
FOR r_itemdetail IN c_itemdetail
LOOP
BEGIN
DELETE FROM ITEMDETAILOUTLAY
WHERE itemdetailid = r_itemdetail.itemdetailid;

COMMIT;

END;
END LOOP;

-- delete the itemdetail
BEGIN
DELETE FROM ITEMDETAIL
WHERE itemid = :p_ItemID;

COMMIT;
END;

-- delete the main item
BEGIN
DELETE FROM ITEM
WHERE itemdid = :p_ItemID;

COMMIT;

p_RowsAffected := SQL%ROWCOUNT;
END;

END DeleteItem;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 3 2010
Added on May 6 2010
6 comments
2,642 views