plsqlsubqueryoracle12cuser-defined-typesbulk-collect

How to bulk collect into a Type where UDT in subquery


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


Solution

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