PL/SQL использует табличную переменную в предложении Where

Является ли CatalogNos типом SQL (т.е. не объявленным в спецификации пакета)? Если так:

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;

«Этот SQL выдает ошибку: PLS-00642: локальные типы коллекций не разрешены в операторах SQL»

Таким образом, CatalogNos не является типом SQL, т.е. это тип PL/SQL, объявленный в спецификации или теле пакета. Сообщение об ошибке совершенно ясно: мы не можем использовать типы PL/SQL в операторах SQL. Так оно и есть.

Самое простое решение — использовать тип SQL.

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

Type created.

SQL> 

Если вы действительно не хотите создавать свой собственный тип (почему бы и нет?), вы можете использовать один из встроенных модулей Oracle. Так:

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

create type CatalogNos is table of VARCHAR2(100);

CREATE OR REPLACE PACKAGE your_package as
   PROCEDURE GET_PART_CHARACTERISTICS (v_catalog_nos_  IN CatalogNos, 
                                       Parts_Char_Cursor out sys_refcursor);
END your_package;

CREATE OR REPLACE PACKAGE BODY your_package as
   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 column_value from table(v_catalog_nos_));
   END GET_PART_CHARACTERISTICS;
END your_package;

Определение типа должно быть объектом базы данных, а не частью пакета, чтобы его можно было использовать в SQL. Как только это верно, вы можете использовать функцию table для ссылки на переменную этого типа в предложении from.

Судя по комментарию, мне кажется, что мне нужно повторить итерацию: чтобы сослаться на пользовательский тип данных в SQL, вы должны определить тип как отдельный объект вне пакета.

Для определяемой пользователем таблицы, содержащей одно безымянное поле, можно использовать либо select *, либо select column_value.

Используйте || для объединения вашего запроса с переменной

что-то типа:

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;