PL/SQL Use Table Variable in Where Clause

Question

I have a table variable that is being passed into a procedure. I would like to use the values in a where clause as below, how do I do this. The first line below is declared in the package definition. The procedure below is in the package body.

type CatalogNos is table of VARCHAR2(100);
PROCEDURE GET_PART_CHARACTERISTICS (v_catalog_nos_  IN CatalogNos, 
                                    Parts_Char_Cursor out sys_refcursor) AS
BEGIN
    OPEN Parts_Char_Cursor FOR
    SELECT * FROM IFSAPP.SALES_PART_CHARACTERISTIC
    WHERE CATALOG_NO IN (select values from v_catalog_nos_);
END GET_PART_CHARACTERISTICS;

Answer 1

Is CatalogNos a SQL type (i.e. not declared in a package spec)? If so:

PROCEDURE GET_PART_CHARACTERISTICS (v_catalog_nos_  IN CatalogNos, 
                                    Parts_Char_Cursor out sys_refcursor) 
AS
BEGIN
    OPEN Parts_Char_Cursor FOR
    SELECT * FROM IFSAPP.SALES_PART_CHARACTERISTIC
    WHERE CATALOG_NO IN (select * from table(v_catalog_nos_));
END GET_PART_CHARACTERISTICS;

---

"This SQL gives an error: PLS-00642: local collection types not allowed in SQL statements"

So CatalogNos is not a SQL type i.e. it is a PL/SQL type declared in a package spec or body. The error message is quite clear: we cannot use PL/SQL types in SQL statements. That's just the way it is.

The simplest solution is to use a SQL type.

SQL> create or replace type CatalogNos is table of VARCHAR2(100);    
  2  /

Type created.

SQL> 

If you really don't want to create your own type (why not?) you can use one of the Oracle built-ins. Like this:

create or replace PROCEDURE GET_PART_CHARACTERISTICS
      (v_catalog_nos_  IN sys.dbms_debug_vc2coll,
       Parts_Char_Cursor out sys_refcursor)
AS
BEGIN
    OPEN Parts_Char_Cursor FOR
    SELECT * FROM IFSAPP.SALES_PART_CHARACTERISTIC
    WHERE CATALOG_NO IN (select * from table(v_catalog_nos_));
END GET_PART_CHARACTERISTICS;
/