I'm trying to create a collection of values for which i plan on running a FORALL DELETE FROM [table] on. When i have a UDT in the subquery nothing seems to get bulk collected into the collection.
This does not seem to be working.
SELECT ATTR1
BULK COLLECT INTO tmpTBL1
FROM Table1
WHERE ATTR1 NOT IN (SELECT ATTR1 FROM TABLE(tmpPList)); --99% sure problem is here.
Have also confirmed that the column sizes in the object type are the same sizes in the Person table. (Thought padding might have been an issue.)
Object Type
CREATE OR REPLACE type dbo.P_REC AS OBJECT
(
ATTR1 VARCHAR2(64 BYTE),
ATTR2 VARCHAR2(128 BYTE),
ATTR3 VARCHAR2(128 BYTE),
ATTR4 VARCHAR2(128 BYTE)
);
Collection Type
CREATE OR REPLACE type dbo.P_REC_LIST is table of P_REC;
Stored Procedure
PROCEDURE Get_PRecList(tmpPList IN P_REC_LIST,
resultCursor out sys_refcursor)
IS
TYPE CNsTable IS TABLE OF PERSON.ATTR1%TYPE INDEX BY PLS_INTEGER;
TYPE TmpTable IS TABLE OF P_REC INDEX BY PLS_INTEGER;
tmpTBL1 CNsTable;
Collection1 TmpTable;
BEGIN
IF tmpPList.count > 0 THEN
SELECT ATTR1
BULK COLLECT INTO tmpTBL1
FROM Table1
WHERE ATTR1 NOT IN (SELECT ATTR1 FROM TABLE(tmpPList)); --99% sure problem is here.
FOR indx IN 1 .. tmpTBL1.COUNT
LOOP
Collection1(Collection1.COUNT + 1) := tmpPList(indx);
END LOOP;
IF Collection1.COUNT > 0 THEN
FORALL ind IN 1 .. Collection1.COUNT
DELETE
FROM PERSON
WHERE ATTR1 = Collection1(ind).ATTR1;
END IF;
Mock Table Data
tmpPList
__________________________________________
|__attr1__||__attr2__||__attr3__||__attr4__|
jdoe John Doe abcd
fmac Frank Mac efgh
wgab Wayne Gab ijkl
Table1
__________________________________________
|__attr1__||__attr2__||__attr3__||__attr4__|
jdoe John Doe abcd
fmac Frank Mac efgh
wgab Wayne Gab ijkl
mkell Mike Kell mnop
Table2
__________________________________________
|__attr1__||__attr2__||__attr3__||__attr4__|
mdoe Mary Doe abcd
jmac John Mac efgh
mgab Mitch Gab ijkl
mkell Mike Kell mnop
tmpTBL1
_________
|__attr1__|
mkell
Collection1
__________________________________________
|__attr1__||__attr2__||__attr3__||__attr4__|
mkell Mike Kell mnop
Before delete - Person
__________________________________________
|__attr1__||__attr2__||__attr3__||__attr4__|
jdoe John Doe abcd
fmac Frank Mac efgh
wgab Wayne Gab ijkl
mkell Mike Kell mnop
After delete - Person
__________________________________________
|__attr1__||__attr2__||__attr3__||__attr4__|
jdoe John Doe abcd
fmac Frank Mac efgh
wgab Wayne Gab ijkl
tmpTBL1 does not get populated with values when I use the TABLE(tmpPList). I'm expecting there to be records removed from person. Trying to find the ATTR1 that exists in PERSON but DO NOT exist in tmpPList.
Update: Using: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
My issue was not here.
SELECT ATTR1
BULK COLLECT INTO tmpTBL1
FROM Table1
WHERE ATTR1 NOT IN (SELECT ATTR1 FROM TABLE(tmpPList));
But instead here. The entire for-loop was not required and the FORALL statement worked fine without it.
FOR indx IN 1 .. tmpTBL1.COUNT
LOOP
Collection1(Collection1.COUNT + 1) := tmpPList(indx);
END LOOP;