title: Oracle: Returning multiple rows in a function
body: <p>I am trying to create a function which would return multiple rows.</p>
  
  <p>Following is my function and type</p>
  
  <pre><code>create or replace type emp_type
  (
  first_name varchar2(20)
  , last_name varchar2(20)
  , depart_name varchar2(20)
  )
  /
  create or replace function get_employee
   (loc in number)
  return emp_type
  as  
     emp_record emp_type;
  begin
      select a.first_name, a.last_name, b.department_name into emp_record.first_name,
  emp_record.last_name,emp_record.depart_name 
     from employees a, departments b 
      where a.department_id=b.department_id and location_id=loc;
  
     return(emp_record);  
  end;
  </code></pre>
  
  <p>And I used </p>
  
  <pre><code>select get_employee(5) from dual;
  </code></pre>
  
  <p>I am getting "<code>exact fetch returns more than requested number of rows</code> " error.
  Later when I used <code>rownum&lt;2</code> in the select query I got "<code>Reference to uninitialized composite</code>".</p>
  
  <p>Could you please help?</p>
  
  <p>Thanks in Advance</p>