Table-valued functions (TVFs) in Oracle

Table-valued functions (TVFs) are a powerful feature in Microsoft SQL Server that allow you to encapsulate reusable logic returning result sets, as if they were regular tables. They integrate seamlessly into SELECT statements, making them ideal for modular, readable, and composable query design.

The following sample shows how to create and use a Table-valued function in MS SQL Server.

CREATE PROCEDURE EmployeesByDept
    @Dept NVARCHAR(50)
AS
BEGIN
    SELECT id, name, dept, salary
    FROM emp
    WHERE UPPER(dept) = UPPER(@Dept);
END;


EXEC EmployeesByDept @Dept = 'IT';

In contrast, Oracle Database does not support native table-valued functions in the same way. While it offers alternatives like pipelined table functions and REF CURSORs, these require more boilerplate code, explicit type declarations, and are generally less straightforward to use, especially for developers coming from a SQL Server background.

In this post, we’ll explore two practical alternatives in Oracle to emulate table-valued function behavior:

REF CURSOR-based functions, which are ideal for client consumption.
Pipelined table functions, which allow set-based processing and can be queried as if they were tables.

Both approaches provide valid workarounds, and we’ll walk through each with clear examples so you can choose the best fit for your use case.

Creating a sample dataset

Let’s start by creating a simple dataset.

CREATE TABLE emp (
  id     NUMBER PRIMARY KEY,
  name   VARCHAR2(50),
  dept   VARCHAR2(50),
  salary NUMBER
);

-- Inserts some sample records.
INSERT INTO emp (id, name, dept, salary) VALUES (1, 'Alice', 'HR', 50000);
INSERT INTO emp (id, name, dept, salary) VALUES (2, 'Bob', 'Finance', 60000);
INSERT INTO emp (id, name, dept, salary) VALUES (3, 'Charlie', 'IT', 70000);
INSERT INTO emp (id, name, dept, salary) VALUES (4, 'Diana', 'Marketing', 55000);
INSERT INTO emp (id, name, dept, salary) VALUES (5, 'Ethan', 'Sales', 52000);
INSERT INTO emp (id, name, dept, salary) VALUES (6, 'Fiona', 'HR', 51000);
INSERT INTO emp (id, name, dept, salary) VALUES (7, 'George', 'Finance', 61000);
INSERT INTO emp (id, name, dept, salary) VALUES (8, 'Hannah', 'IT', 72000);
INSERT INTO emp (id, name, dept, salary) VALUES (9, 'Ian', 'Marketing', 56000);
INSERT INTO emp (id, name, dept, salary) VALUES (10, 'Jane', 'Sales', 53000);
INSERT INTO emp (id, name, dept, salary) VALUES (11, 'Kevin', 'HR', 54000);
INSERT INTO emp (id, name, dept, salary) VALUES (12, 'Laura', 'Finance', 62000);
INSERT INTO emp (id, name, dept, salary) VALUES (13, 'Mike', 'IT', 73000);
INSERT INTO emp (id, name, dept, salary) VALUES (14, 'Nina', 'Marketing', 57000);
INSERT INTO emp (id, name, dept, salary) VALUES (15, 'Oscar', 'Sales', 54000);
INSERT INTO emp (id, name, dept, salary) VALUES (16, 'Paula', 'HR', 55000);
INSERT INTO emp (id, name, dept, salary) VALUES (17, 'Quentin', 'Finance', 63000);
INSERT INTO emp (id, name, dept, salary) VALUES (18, 'Rachel', 'IT', 74000);
INSERT INTO emp (id, name, dept, salary) VALUES (19, 'Steve', 'Marketing', 58000);
INSERT INTO emp (id, name, dept, salary) VALUES (20, 'Tina', 'Sales', 55000);

COMMIT;

REF CURSOR-based functions

A REF CURSOR is a PL/SQL data type in Oracle that acts as a pointer to a result set returned by a query. It allows you to return query results from a stored procedure or function to the calling client (e.g., SQL Developer, Java, Python) for dynamic, runtime fetching of rows.

CREATE OR REPLACE PACKAGE emp_pkg AS
  TYPE emp_ref_cursor IS REF CURSOR;
END emp_pkg;
/


CREATE OR REPLACE FUNCTION EmployeesByDept(DeptName IN VARCHAR2)
  RETURN emp_pkg.emp_ref_cursor
AS
  emp_cursor emp_pkg.emp_ref_cursor;
BEGIN
  OPEN emp_cursor FOR
    SELECT id, name, dept, salary
    FROM emp
    WHERE UPPER(dept) = UPPER(DeptName);

  RETURN emp_cursor;
END EmployeesByDept;
/

VARIABLE rc REFCURSOR;
BEGIN
  :rc := EmployeesByDept('IT');
END;
/
PRINT rc;


        ID NAME                                               DEPT                                                   SALARY
---------- -------------------------------------------------- -------------------------------------------------- ----------
         3 Charlie                                            IT                                                      70000
         8 Hannah                                             IT                                                      72000
        13 Mike                                               IT                                                      73000
        18 Rachel                                             IT                                                      74000

Pipelined table functions

A pipelined table function in Oracle is a special kind of PL/SQL function that returns a collection of rows one at a time, or “pipelined,” to the SQL engine. This allows the function to behave like a table and be queried directly using SELECT statements — just like a table-valued function in SQL Server.

DEV GYM – Get Started with PL/SQL Table Functions

CREATE OR REPLACE TYPE emp_obj AS OBJECT (
  id     NUMBER,
  name   VARCHAR2(100),
  dept   VARCHAR2(50),
  salary NUMBER
);
/


CREATE OR REPLACE TYPE emp_table AS TABLE OF emp_obj;
/

CREATE OR REPLACE FUNCTION EmployeesByDept(DeptName IN VARCHAR2)
  RETURN emp_table PIPELINED
AS
BEGIN
  FOR rec IN (
    SELECT id, name, dept, salary
    FROM emp
    WHERE UPPER(dept) = UPPER(DeptName)
  )
  LOOP
    PIPE ROW(emp_obj(rec.id, rec.name, rec.dept, rec.salary));
  END LOOP;

  RETURN;
END EmployeesByDept;
/


SELECT * FROM TABLE(EmployeesByDept('IT'));

To wrap up

REF CURSORs and pipelined table functions both allow Oracle PL/SQL to return sets of rows, but they differ significantly in usage and flexibility. REF CURSORs are primarily designed for returning result sets to client applications or PL/SQL blocks, making them ideal for integration with front-end tools or external code. However, they cannot be directly queried in SQL, which limits their use in composable queries or views. In contrast, pipelined table functions integrate seamlessly into SQL queries and can be used in joins, filters, or subqueries as if they were real tables. They require more setup, including the definition of object and collection types, but offer a more SQL-friendly alternative for scenarios where the result needs to be consumed directly within the database. Each method suits different needs: REF CURSORs for external consumption, pipelined functions for internal SQL processing.