SQL & PL/SQL

Hi,

I am trying to implement PL/SQL using bind variables to delete records from "item_progress" table in simple join query where I am passing itemName, itemLoc and orderLoc in the delete query within pl/sql block.

Please suggest, it should be easy for oracle experts...

a) I need to first delete records from item_progress table using join query and then update status in renew_item table.
b) It works fine if I use simple delete with one condition i.e. ('DELETE from item_progress where itemname=:1' using itemname1).

c) But it gives Error when I run it using delete with join :
---------------------------------------
*ORA-00920: invalid relational operator
ORA-06512: at "REQUEUE", line 14 (This is the delete with join... 'DELETE FROM item_progress WHERE item_id IN (SELECT ip.item_id'...)....'
ORA-06512: at line 10
Process exited.*
---------------------------------------

create or replace PROCEDURE REQUEUE (itemName IN VARCHAR2 ,
itemLoc IN VARCHAR2 ,
orderLoc IN VARCHAR2 ) is
itemName1 varchar2(30);
itemLoc1 varchar2(30);
orderLoc1 varchar2(30);
BEGIN
orderLoc1 := 'C:/local/test';
itemLoc1 := '//L123/R';
itemName1 := 'AAA';

execute immediate

'DELETE FROM item_progress WHERE item_id IN (SELECT ip.item_id FROM item_progress ip, item_order io WHERE io.orderlocation := 1 AND ip.order_id = io.order_id UNION SELECT item_id FROM item_progress WHERE ITEMLOCATION :=2 ) AND itemname:=3' using orderloc1, itemloc1, itemname1 ;

--'DELETE from item_progress where itemname=:1' using itemname1; -- THIS WORKS FINE
-- Once above query is success update the status in other table
--Update renew_item set Status='RENEWED' where ITEMNAME=:1, ITEMLOCATION = :2 using itemname1, itemloc1 ;

commit;
END REQUEUE;