oracle-databaseplsqloracle10gvarray

PL/SQL use VARRAY in IN CLAUSE


Is it possible to use VARRAY in IN CLAUSE of pl/sql?


Solution

  • Yes, you can, provided that the VARRAY type is a global type (and not local to some PL/SQL code):

    CREATE OR REPLACE TYPE str_tab_type IS VARRAY(10) OF VARCHAR2(200);
    
    DECLARE
      l_str_tab str_tab_type;
      l_count NUMBER;
    BEGIN
      l_str_tab := str_tab_type();
      l_str_tab.extend(2);
      l_str_tab(1) := 'TABLE';
      l_str_tab(2) := 'INDEX';
    
      SELECT COUNT(*) INTO l_count
      FROM all_objects
      WHERE object_type IN (SELECT COLUMN_VALUE FROM TABLE(l_str_tab));
    END;
    /