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;

APC · Accepted Answer · 2011-12-29 16:52:55Z

5

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

edited Dec 29, 2011 at 16:52

answered Dec 28, 2011 at 20:32

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adam Over a year ago

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

Allan · Accepted Answer · 2011-12-29 19:14:43Z

2

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;

The type definition must be a database object, not part of a package, in order to be used in SQL. Once that's true, you can use the table function to reference a variable of that type in the from clause.

Based on the comment, it seems that I need to re-iterate: in order to reference a user-defined datatype in SQL, you must define the type as a separate object outside of the package.

For a user-defined table that contains a single unnamed field, you can use either select * or select column_value.

edited Dec 29, 2011 at 19:14

answered Dec 28, 2011 at 20:31

Allan

17.5k5 gold badges54 silver badges73 bronze badges

3 Comments

Adam Over a year ago

If your first line in my package definition, then the package will not compile.

Florin Ghita Over a year ago

CREATE OR REPLACE PACKAGE BODY

Adam Over a year ago

Ok, this all works except for the values keyword in the "IN" statement. It compiles, if I replace it with a *.

Emmanuel N · Accepted Answer · 2011-12-28 20:33:33Z

0

Use || for to concatenate your query with a variable

something like:

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;

edited Dec 28, 2011 at 20:33

answered Dec 28, 2011 at 20:06

Emmanuel N

7,4592 gold badges30 silver badges37 bronze badges

3 Comments

Adam Over a year ago

I am still getting compile errors... ORA-00936: missing expression, which points to the beginning of "values".

user330315 Over a year ago

The & is only used by SQL*Plus on the client side to define (interactive) parameters. This won't work at runtime inside a stored procedure.

Allan Over a year ago

Have you actually tried this? Oracle can't reference a user-defined table type without the use of the table function. This proposed solution will not work. In addition, even if it would work, concatenating a variable's value into a DML statement is an extremely poor practice.

Collectives™ on Stack Overflow

PL/SQL Use Table Variable in Where Clause

3 Answers 3

1 Comment

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related