PL/SQL - How to use an array in an IN Clause

Question

I'm trying to use an array of input values to my procedure in an IN Clause as part of the where clause of a cursor. I know that this has been asked before, but I haven't seen how to make my syntax compile correctly.

In the package specification, the type is

TYPE t_brth_dt IS TABLE OF sourceTable.stdt_brth_dt%TYPE INDEX BY PLS_INTEGER;

sourceTable.std_brth_dt is a date column in the table.

Simplified version of my cursor is in the package body is -

 cursor DataCursor_Sort( p_brth_dt in t_brth_dt) is
    SELECT *
      FROM sourceTable 
     WHERE a.brth_dt IN (select column_value 
                           from table(p_brth_dt))

When I try to compile this, I'm getting the following errors.

• [1]:(Error): PLS-00382: expression is of wrong type
• [2]:(Error): PL/SQL: ORA-22905: cannot access rows from a non-nested table item

I know this looks similar to other questions, but I don't understand what the syntax error is.

Answer 1

In order to use collection defined as a nested table or an associative array in the from clause of a query you either should, as @Alex Poole correctly pointed out, create a schema level (SQL) type or use one, that is available to you trough ODCIConst package - odcidatelist as you intend to use a list of dates. For example, your cursor definition might look like this:

cursor DataCursor_Sort(p_brth_dt in sys.odcidatelist) is
  select *
    from sourceTable 
   where a.brth_dt IN (select column_value 
                         from table(p_brth_dt))

OR

cursor DataCursor_Sort(p_brth_dt in sys.odcidatelist) is
  select s.*
    from sourceTable      s
    join table(p_brth_dt) t
      on (s.brth_dt = t.column_value)

Note: You should take into consideration the time part of a date when performing a date comparison. If you want to compare date part only it probably would be useful to get rid of time part by using trunc() function.

Answer 2

It is possible to use a PL/SQL-defined nested table type (as opposed to a SQL-defined nested table type) indirectly in an IN clause of a SELECT statement in a PL/SQL package. You must use a PIPELINED function as an intermediary. It felt kind of clever to write, but I don't believe in its fundamental usefulness.

CREATE OR REPLACE PACKAGE so18989249 IS

   TYPE date_plsql_nested_table_type IS TABLE OF DATE;
   dates date_plsql_nested_table_type;

   FUNCTION dates_pipelined RETURN date_plsql_nested_table_type PIPELINED;

   PROCEDURE use_plsql_nested_table_type;

END so18989249;
/

CREATE OR REPLACE PACKAGE BODY so18989249 IS

   FUNCTION dates_pipelined RETURN date_plsql_nested_table_type
      PIPELINED IS
   BEGIN
      IF (dates.count > 0)
      THEN
         FOR i IN dates.first .. dates.last
         LOOP
            IF (dates.exists(i))
            THEN
               PIPE ROW(dates(i));
            END IF;
         END LOOP;
      END IF;
   END;

   PROCEDURE use_plsql_nested_table_type IS
   BEGIN
      dates := NEW date_plsql_nested_table_type();

      -- tweak these values as you see fit to produce the dbms_output results you want
      dates.extend(5);
      dates(1) := DATE '2013-12-25';
      dates(2) := DATE '2013-01-01';
      dates(3) := DATE '2013-07-01';
      dates(4) := DATE '2013-09-03';
      dates(5) := DATE '2008-11-18';

      FOR i IN (SELECT o.owner,
                       o.object_name,
                       o.object_type,
                       to_char(o.last_ddl_time, 'YYYY-MM-DD') AS last_ddl
                  FROM all_objects o
                 WHERE trunc(o.last_ddl_time) IN 
                       (SELECT column_value FROM TABLE(dates_pipelined)) 
                       --uses pipeline function which uses pl/sql-defined nested table
      )
      LOOP
         dbms_output.put_line('"' || i.owner || '"."' || i.object_name || '" ("' || i.object_type || ') on ' || i.last_ddl);
      END LOOP;

   END;

END so18989249;
/

begin so18989249.use_plsql_nested_table_type; end;
/