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.
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.