Runtime error of "ORA-00902: invalid datatype" what I am doing wrong?

I have a package/procedure that is called from a web service. It returns a refcursor. I am trying to optimize the filtering as it actually has a lot of filtering options and what is below is just a tiny example. This is an attempt to limit the records used later.

I am getting a runtime error: Error at line 1 ORA-00902: invalid datatype ORA-06512: at "MARK.PKG_PRODUCTS", line 13 ORA-06512: at line 9 It worked when it was just a select statement but when I changed it to insert into a table I am now getting this error.

Setup and package below

CREATE TABLE PRODUCTS (PRODUCT_ID VARCHAR2(10), VENDOR VARCHAR2(10), ITEM VARCHAR(10));
CREATE TABLE PRODUCT_FILTER (PRODUCT_ID VARCHAR2(10));
CREATE TABLE PRODUCT_LOG (LOG_DATE DATE, LOG_TEXT VARCHAR2(4000));

CREATE OR REPLACE PACKAGE PKG_PRODUCTS AS
  TYPE t_filter IS TABLE OF VARCHAR2(250)
    INDEX BY BINARY_INTEGER;

  PROCEDURE SET_FILTER(p_vendor IN t_filter, p_item IN t_filter);
END PKG_PRODUCTS;
/

CREATE OR REPLACE PACKAGE BODY PKG_PRODUCTS AS
  PROCEDURE SET_FILTER(p_vendor IN t_filter, p_item IN t_filter)
  IS
  v_text VARCHAR2(4000);
  
  BEGIN
    SELECT LISTAGG(Column_Value, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY 1) INTO v_text FROM TABLE(p_vendor);
    INSERT INTO PRODUCT_LOG (LOG_DATE, LOG_TEXT) VALUES (SYSDATE, 'p_vendor:'||v_text);

    SELECT LISTAGG(Column_Value, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY 1) INTO v_text FROM TABLE(p_item);
    INSERT INTO PRODUCT_LOG (LOG_DATE, LOG_TEXT) VALUES (SYSDATE, 'p_item:'||v_text);

    INSERT INTO PRODUCT_FILTER (PRODUCT_ID)
      SELECT PRODUCT_ID FROM PRODUCTS
        WHERE
            (p_vendor(1) IS NULL 
            OR VENDOR IN (SELECT * FROM TABLE(p_vendor)))
          AND
            (p_item(1) IS NULL
            OR ITEM IN (SELECT * FROM TABLE(p_item)));
  END SET_FILTER;
END PKG_PRODUCTS;
/

-- To run 
declare
  P_VENDOR PKG_PRODUCTS.t_filter;
  P_ITEM PKG_PRODUCTS.t_filter;

begin
  P_VENDOR(1) := 'Vendor1';
  P_ITEM(1) := 'Item1';

  PKG_PRODUCTS.SET_FILTER(
      P_VENDOR,
      P_ITEM
  );
end;

I get this error at runtime: Error at line 1 ORA-00902: invalid datatype ORA-06512: at "MARK.PKG_PRODUCTS", line 13 ORA-06512: at line 9

I'm using Oracle 19c

Any assistance would be appreciated

I have limited the error down to "OR VENDOR IN (SELECT * FROM TABLE(p_vendor)))" but having trouble figuring out the exact cause.

Solution

TYPE t_filter IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;

Declares a PL/SQL associative array. It cannot be use in SQL statements - only PL/SQL statements.

If you want to use a collection then use a nested-table type (or a VARRAY) and define it in the SQL scope.

CREATE TYPE varchar2_250_list IS TABLE OF VARCHAR2(250);

Then:

CREATE OR REPLACE PACKAGE PKG_PRODUCTS AS
  PROCEDURE SET_FILTER(
    p_vendor IN varchar2_250_list,
    p_item   IN varchar2_250_list
  );
END PKG_PRODUCTS;
/

CREATE OR REPLACE PACKAGE BODY PKG_PRODUCTS AS
  PROCEDURE SET_FILTER(
    p_vendor IN varchar2_250_list,
    p_item   IN varchar2_250_list
  )
  IS
    v_text VARCHAR2(4000);
  
  BEGIN
    INSERT INTO PRODUCT_LOG (LOG_DATE, LOG_TEXT)
      SELECT SYSDATE,
             'p_vendor:'
             || LISTAGG(Column_Value, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY 1)
      FROM   TABLE(p_vendor);


    INSERT INTO PRODUCT_LOG (LOG_DATE, LOG_TEXT)
      SELECT SYSDATE,
             'p_item:'
             ||LISTAGG(Column_Value, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY 1)
      FROM   TABLE(p_item);

    INSERT INTO PRODUCT_FILTER (PRODUCT_ID)
      SELECT PRODUCT_ID
      FROM   PRODUCTS
      WHERE  ( p_vendor IS EMPTY
               OR VENDOR MEMBER OF p_vendor)
      AND    ( p_item IS EMPTY
               OR ITEM MEMBER OF p_item);
  END SET_FILTER;
END PKG_PRODUCTS;
/

Note: If you decide to use a VARRAY (rather than a nested-table type) then VARRAYs do not support the MEMBER OF operator.

fiddle