Create a function that returns a table

In SQL Server it is easy to create a function that returns a table when used in the from clause. In Oracle this is not as easy as it seems but since my current client uses Oracle, I was forced to find a solution so here it is.

To create a function that returns a table in Oracle you need to follow a few steps:

• Create a type that corresponds to a single record
  
• Create a type that corresponds to a table and that has the previously declared type as recordtype
  
• Create the function

To use the function, you need to include it in the TABLE() function.

Here is an example on how to do it:

• First I create a simple table that will be used for the sample.
  

  -- Create a dummy table to show how it works.
  -- The table contains a hierarchy between boss 
  -- and employee.
  CREATE TABLE employee
  (
    emp_id       NUMBER(10),
    full_name    VARCHAR2(50),
    emp_boss_id  NUMBER(10)
  );
  
  -- Insert dummy data:
  INSERT INTO employee VALUES (1, 'Geert Verhoeven', null);
  INSERT INTO employee VALUES (2, 'Ford Parker', null);
  INSERT INTO employee VALUES (3, 'Al Bino', 1);
  INSERT INTO employee VALUES (4, 'April May', 1);
  INSERT INTO employee VALUES (5, 'Abbie Birthday', 3);
  INSERT INTO employee VALUES (6, 'Gene E. Yuss', 3);
  INSERT INTO employee VALUES (7, 'Juana Bea', 3);
  INSERT INTO employee VALUES (8, 'Willie Maykit', 4);
  INSERT INTO employee VALUES (9, 'Zoltan Pepper', 4);
  INSERT INTO employee VALUES (10, 'Scott Shawn DeRocks', 4);
  COMMIT;

  
  
• Then I create the types needed to be able to return a table
  

  -- Create a type that corresponds to a row that you want to have returned.
  CREATE TYPE ind_employee AS OBJECT (emp_id NUMBER(10), full_name  VARCHAR2(50));
  --
   Create a type that corresponds to a table of rows with the specified type.
  CREATE TYPE ind_employee_table AS TABLE OF ind_employee;

• After this is done, you can create a function that returns a table.
  

  -- Create the function.
  CREATE OR REPLACE FUNCTION FN_GET_EMPLOYEE_DESCENDANTS (
      pin_emp_id IN NUMBER
  ) RETURN ind_employee_table PIPELINED 
  IS  
      
  TYPE t_ref_cursor IS REF CURSOR;    
  lc_employees        t_ref_cursor;
  lr_out_rec          ind_employee := ind_employee(NULL, NULL);
      
  BEGIN
      OPEN lc_employees FOR 
          -- The connect by prior handles the hierarchy.
          SELECT 
              emp_id,
              full_name 
          FROM employee    
          START WITH emp_id = pin_emp_id
          CONNECT BY PRIOR emp_id = emp_boss_id;
    
          LOOP
              FETCH lc_employees 
              INTO 
                  lr_out_rec.emp_id,    
                  lr_out_rec.full_name;
              
              EXIT WHEN lc_employees%NOTFOUND;
              PIPE ROW(lr_out_rec);
          END LOOP;
      CLOSE lc_employees;
      RETURN;
  END;
  

  
  REMARKS: The PIPELINED command and PIPE ROW() method, work together to make sure that each row is returned to the caller immediately after it is processed. This helps to avoid having big objects in memory.
  
  
• To use the function, you need to use the TABLE() function.
  

  -- Sample query:
  SELECT * FROM TABLE(FN_GET_EMPLOYEE_DESCENDANTS(1));

  
  
• This gives the following results:
  

  EMP_ID    FULL_NAME
  
  1    Geert Verhoeven
  3    Al Bino
  5    Abbie Birthday
  6    Gene E. Yuss
  7    Juana Bea
  4    April May
  8    Willie Maykit
  9    Zoltan Pepper
  10    Scott Shawn DeRocks