can you reference a collection of type table in a sql statement?

[crowsario]

Hello All,

I am very new at this so any help would be much appreciated...

I am trying to reference a table collection in a sql statement in a stored proc... and keep getting the following error in the last criteria of the where clause when trying to compile it:
"PLS-00383: type mismatch found at 'TBLISOLATEDZIPS.ZIP_CODE' inside an IN or NOT IN clause"


here is the stored procedure code:
CREATE OR REPLACE PROCEDURE SPMULTIPLE_ZIP IS
--get all states to loop through
CURSOR TMP_CURSOR IS
SELECT distinct STATE_ABBREV
FROM TBLISOLATEDZIPS;

--get list of all zips where they cross county lines
cursor tmpZips is
SELECT DISTINCT ZIP_CODE
FROM TBLISOLATEDZIPS GROUP BY ZIP_CODE HAVING COUNT(COUNTY_NO)>1;

type zip_tab_t is table of TBLISOLATEDZIPS.ZIP_CODE%type;

zip_tab zip_tab_t;


BEGIN

--truncate the table
EXECUTE IMMEDIATE 'TRUNCATE TABLE TBLUSPS_MULTIPLEZIPAT';

commit;

--fill pl/sql table with all zips returned to cursor tmpZips
for tmps in tmpZips loop
zip_tab(tmpZips%rowcount):= tmps.zip_code;
end loop;


--for every state loop
for tmp_record in tmp_cursor loop
INSERT INTO TBLUSPS_MULTIPLEZIPAT(ZIP, STREET, SCC)
--get all streets and SCC's that are in these zip codes for the current state
SELECT DISTINCT tblisolatedzips.ZIP_CODE, ZIP4_DETAIL.STREET_NAME, tblisolatedzips.SCC
FROM ZIP4_DETAIL,TBLISOLATEDZIPS
where (tblisolatedzips.STATE_ABBREV=tmp_record.state_abbrev) and (ZIP4_DETAIL.COUNT_NO=tblisolatedzips.COUNTY_NO)
AND (ZIP4_DETAIL.STATE_ABBREV=tblisolatedzips.STATE_ABBREV) and (tblisolatedzips.zip_code in (zip_tab));
commit;
end loop;


EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END SPMULTIPLE_ZIP;
/


thanks
-Crow