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