title: PLSQL NULL or Empty collection in table SELECT
body: <p>I am trying to figure out a clean way of qualifying on a table type that may be null or empty. Below is what I have tried so far but that results in a </p>
  
  <blockquote>
    <p>ORA-01427: single-row subquery returns more than one row</p>
  </blockquote>
  
  <pre><code>DECLARE
      V_TEMP_IDS V500.T_TEMP_IDS := V500.T_TEMP_IDS(123, 124);
  
      TYPE T_RESULTS IS TABLE OF NUMBER;
      V_RESULTS T_RESULTS;
  BEGIN
  
      SELECT TEMP_ID BULK COLLECT INTO V_RESULTS FROM PEOPLE
      WHERE ACTIVE = 1
          AND TEMP_ID IN (COALESCE(NULLIF((SELECT * FROM TABLE(V_TEMP_IDS)), 0), TEMP_ID));
  
      dbms_output.put_line(SQL%ROWCOUNT);
  
  END;
  </code></pre>
  
  <p>I would like to select all rows if the collection is null or empty but if it has entries, such as in this example, to include them in the qualification. Hope that makes sense!</p>
  
  <p>Thanks in advance!</p>