title: Oracle Stored Procedure: Returning a Multiple Rows
body: <p>In my stored procedure it needs to get inputs and return multiple rows to a front-end application.</p>
  
  <p>However the stored Procedure produces an errors:</p>
  
  <ul>
  <li>SQL Statement ignored</li>
  <li>not enough values</li>
  </ul>
  
  <p>How to resolve?</p>
  
  <hr>
  
  <p><strong>Object type</strong></p>
  
  <pre><code>CREATE TYPE org_rspnsble_prsns_type 
      AS OBJECT (
      "appId" varchar2, 
      "orgId" varchar2, 
      "domainId" varchar2, 
      "leadName" varchar2, 
      "personId" number
  );
  </code></pre>
  
  <p><strong>Table type</strong></p>
  
  <pre><code>CREATE TYPE org_rspnsble_prsns_table 
  AS TABLE OF org_rspnsble_prsns_type;
  </code></pre>
  
  <p><strong>Stored Procedure</strong></p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE GetNames( appIdInput IN varchar2, orgIdInput IN varchar2, p_arr OUT org_rspnsble_prsns_table )
  AS
  BEGIN
      SELECT "appId", "orgId", "domainId", "leadName", "personId"
      BULK COLLECT INTO p_arr
      FROM (
          select "appId", "orgId", "domainId", "leadName", "personId"
          from tableA
      UNION
          select "appId", "orgId", "domainId", "leadName", "personId"
          from tableB
      )
      WHERE "appId" = appIdInput
      AND "orgId" = orgIdInput;
  END;
  </code></pre>