Oracle: Returning multiple rows in a function

Question

I am trying to create a function which would return multiple rows.

Following is my function and type

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;

And I used

select get_employee(5) from dual;

I am getting "exact fetch returns more than requested number of rows " error. Later when I used rownum<2 in the select query I got "Reference to uninitialized composite".

Could you please help?

Thanks in Advance

What does it mean to you to return multiple rows? Do you want to return a collection of emp_type objects (I assume you omitted the as object from your create or replace type statement)? Do you want to write a pipelined table function? Do you want to return a sys_refcursor? Something else? — Justin Cave
– Justin Cave, Commented Mar 17, 2014 at 17:27
Hi Justin.. I would like to return it as sys_refcursor returning more than 1 row. I now added "as object" but still getting the error "Reference to uninitialized composite". — Kerri Williams
– Kerri Williams, Commented Mar 17, 2014 at 17:31

Justin Cave · Accepted Answer · 2014-03-17 17:33:53Z

6

If you want to return a sys_refcursor, there is no reason to declare the object type or to try to return an object type. Just return a sys_refcursor.

create or replace function get_employee
  (p_loc in number)
  return sys_refcursor
as  
  l_rc sys_refcursor;
begin
  open l_rc
   for select a.first_name, a.last_name, b.department_name 
         from employees a, 
              departments b 
        where a.department_id=b.department_id 
          and location_id=p_loc;

  return l_rc; 
end;

answered Mar 17, 2014 at 17:33

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kerri Williams Over a year ago

Hi Justin.. yesss.. it worked.. thank you so much for your help.:)

Collectives™ on Stack Overflow

Oracle: Returning multiple rows in a function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related