title: Oracle - PLS-00642: local collection types not allowed in SQL statements
body: <p>I am new to programming in ORACLE and I am trying to compare a table column value to a passed in array and I am having a rather frustrating time in doing so.</p>
  
  <p>Here is the Type Declaration from the package head.</p>
  
  <pre><code>TYPE T_STRING_ARRAY IS TABLE OF VARCHAR2(5); 
  </code></pre>
  
  <p>and here is the the function that is using it. </p>
  
  <pre><code>create or replace PACKAGE BODY TEST_PACK IS 
  
    FUNCTION TEST_LOG_FN 
    (
          PI_START_DATE IN VARCHAR2,
          PI_END_DATE IN VARCHAR2, 
          PI_LOG_TYPE IN T_STRING_ARRAY
    )
     RETURN T_REF_CURSOR
      AS  
      PO_RESULT T_REF_CURSOR;
  
    BEGIN   
  
  
    OPEN PO_RESULT FOR
    SELECT
          EL.ENTRY_BASE_LOG_ID,
            EL.APP_NAME,
            EL.APP_MODULE,
            EL.CREATION_DATE,
            EL.APP_STATUS,
            EL.LOG_TYPE
      FROM 
            LG_ENTRY_BASE_LOG EL
    WHERE     
            CREATION_DATE &gt; PI_START_DATE AND
            CREATION_DATE  &lt; PI_END_DATE AND              
           (EL.LOG_TYPE IN PI_LOG_TYPE OR PI_LOG_TYPE = NULL);
  
    RETURN 
        PO_RESULT;
  
  EXCEPTION
        WHEN NO_DATA_FOUND THEN 
             RETURN NULL;
         END TEST_LOG_FN;
  END;
  </code></pre>
  
  <p>The error I am getting is PLS-00642: local collection types not allowed in SQL statements. I have read online </p>
  
  <p>"To avoid the PLS-00642, the collection will need to be defined at the schema level; therefore, you would need to define the varray table as a real table, using Oracle DDL with the CREATE TYPE syntax. "  </p>
  
  <p><a href="http://www.dba-oracle.com/t_pls_00642_local_collection_types_not_allowed_in_sql_statement.htm" rel="nofollow">http://www.dba-oracle.com/t_pls_00642_local_collection_types_not_allowed_in_sql_statement.htm</a></p>
  
  <p>I am not sure how to do that nor have I found any references online that I could use. Can someone help me with that? If someone knows an easier way to see if a string exists in an array, that is a perfectly acceptable answer as well.</p>