Oracle Database Discussions

Hi All,

I have a table with nested table and i want to delete some rows from that table.

Example:

I have create two table, and i want to delete matching rows from that table, The table like

CREATE OR REPLACE TYPE ANN_OBJ AS OBJECT (COL1 NUMBER,
COL2 NUMBER
);

CREATE OR REPLACE TYPE ANN_NES AS TABLE OF ANN_OBJ;

CREATE TABLE ANN49(ID1 NUMBER,ID2 ANN_NES)
NESTED TABLE ID2 STORE AS ANN_NESTAB1 RETURN AS LOCATOR;

CREATE TABLE PRICKS49(ID1 NUMBER,ID2 ANN_NES)
NESTED TABLE ID2 STORE AS ANN_NESTAB2 RETURN AS LOCATOR;


insert into ANN49 values(1,1);
insert into ANN49 values(2,1);
insert into ANN49 values(3,2);
insert into ANN49 values(4,3);
insert into ANN49 values(5,3);

insert into PRICKS49 values(1,1);
insert into PRICKS49 values(2,1);
insert into PRICKS49 values(3,2);

now, i do

select b.col1,b.col2 from ANN49 a,table(a.id2) b where exists(select d.col1,d.col2 from PRICKS49 c,table(C.ID2) d
where b.col1=d.col1 AND b.col2=d.col2);

the rows are fetched like,

COL1 COL2
---------------------- ----------------------
1 1
2 1
3 2

i want to delete these rows from that ANN49 table
then i wrote the query like,

DELETE from ANN49 a,table(a.id2) b where exists(select d.col1,d.col2 from PRICKS49 c,table(C.ID2) d
where b.col1=d.col1 AND b.col2=d.col2);

but, i got error.........

So, Can anyone please tell the query to delete that rows...

Please do the needful





thanks
ann