title: join oracle database table with table of custom type
body: <p>I want to join a table say EMPLOYEE with another custom oracle type MATCHING_CRITERIA_LIST.
  MATCHING_CRITERIA_LIST is a table of a custom oracle type CRITERIA. All DDL as follows:</p>
  
  <pre><code>CREATE OR REPLACE
  type CRITERIA as object (
  DOB DATETIME,
  SALARY NUMBER
  );
  
  CREATE OR REPLACE TYPE
  MATCHING_CRITERIA_LIST IS TABLE OF CRITERIA;
  
  CREATE TABLE EMPLOYEE{
      ID NUMBER PRIMARY KEY NOT NULL,
      NAME VARCHAR(20 BYTE),
      DOB DATETIME,
      SALARY NUMBER
  }
  </code></pre>
  
  <p>What I am actually trying to achieve is,</p>
  
  <pre><code>var allEmployeeList = new List&lt;Employee&gt;();
  var filteredList = new List&lt;Employee&gt;();
  var matchingCriteria = new List&lt;MatchingCritera&gt;{
      new MatchingCritera(){DOB = &lt;date1&gt;, salary = &lt;sal1&gt;},
      new MatchingCritera(){DOB = &lt;date2&gt;, salary = &lt;sal2&gt;},
      new MatchingCritera(){DOB = &lt;date3&gt;, salary = &lt;sal1&gt;}
  }
  foreach(var emp in allEmployeeList)
  {
      foreach(var criteria in matchingCriteria)
      {
          if(emp.DOB == criteria.DOB &amp;&amp; emp.salary = criteria.salary)
          {
              filteredList.Add(emp);
          }
      }
  }
  </code></pre>
  
  <p>I want this same logic to be in SP. I am currently doing as follows which is working fine.</p>
  
  <pre><code>CREATE OR REPLACE
  type IDTYPE as object (
  id NUMBER
  );
  CREATE OR REPLACE
  type IDTABLETYPE IS TABLE OF IDTYPE;
  
  CREATE OR REPLACE PROCEDURE GET_FILTERED_EMPLOYEE (
     IN_CRITERIA_LIST   IN       MATCHING_CRITERIA_LIST,
     CUR_OUT            OUT      sys_refcursor
  )
  IS
  
  V_ID_TABLE IDTABLETYPE;
  V_TEMP_ID_COLL EMPLOYEE_ID;
  
  BEGIN
  
     V_ID_TABLE := IDTABLETYPE();
     V_TEMP_ID_COLL := EMPLOYEE_ID();
  
     IF IN_CRITERIA_LIST.COUNT &gt; 0 THEN
       FOR i IN IN_CRITERIA_LIST.FIRST .. IN_CRITERIA_LIST.LAST
       LOOP
          SELECT EMP.ID BULK COLLECT INTO V_TEMP_ID_COLL FROM EMPLOYEE EMP WHERE
          EMP.DOB = IN_CRITERIA_LIST(i).DOB
          AND EMP.SALARY = IN_CRITERIA_LIST(i).SALARY
          ORDER BY EMP.ID DESC;
  
          IF (V_TEMP_ID_COLL.COUNT &gt; 0) THEN
           FOR j IN V_TEMP_ID_COLL.FIRST .. V_TEMP_ID_COLL.LAST
            LOOP
              V_ID_TABLE.extend();
              V_ID_TABLE(V_ID_TABLE.count) := IDTYPE(TO_NUMBER(V_TEMP_ID_COLL(j)));
            END LOOP;
          END IF;
       END LOOP;
     END IF;
  
     OPEN CUR_OUT FOR
     SELECT * FROM EMPLOYEE EMP WHERE EMP.ID IN (SELECT * FROM TABLE(V_ID_TABLE));
  END;
  </code></pre>
  
  <p>I want to remove the for loop on IN_CRITERIA_LIST, as it is impacting the performance, and do something like below:</p>
  
  <pre><code>SELECT * FROM EMPLOYEE EMP
  INNER JOIN MATCHING_CRITERIA_LIST MCL ON 
  EMP.DOB = MCL.DOB
  AND EMP.SALARY = MCL.SALARY
  ORDER BY TD.TRANS_DASHBOARD_ID DESC;
  </code></pre>
  
  <p>Can someone guide how I can join my custom table of UDT with and oracle table?</p>