A pipelined function in Oracle SQL is a special type of table function that allows you to return rows one at a time, rather than as a complete collection. This is particularly useful in APEX applications when working with large datasets, as it improves performance by streaming data instead of storing it in memory first.
This tutorial explains how to create a pipelined function that takes a parameter, making it dynamic and reusable in APEX reports, forms, or other database operations.
Understanding Pipelined Functions
A pipelined function:
Returns a table-like structure that can be queried like a normal table.
Streams results row by row, reducing memory usage.
Can take input parameters to filter or modify results dynamically.
Step 1: Create a Custom Object Type
Before defining the pipelined function, a custom object type is required to define the structure of the returned rows.
CREATE OR REPLACE TYPE emp_record_type AS OBJECT (
emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER
);
/
This object type represents a single row containing an emp_id, emp_name, and salary.
Step 2: Create a Table Type
A table type is required to hold multiple instances of the object type, allowing the function to return multiple rows.
CREATE OR REPLACE TYPE emp_table_type AS TABLE OF emp_record_type;
/
This defines a table-like collection of emp_record_type objects.
Step 3: Create the Pipelined Function
Now, we create the pipelined function that returns employees earning above a certain salary, which is passed as a parameter.
CREATE OR REPLACE FUNCTION get_high_earning_employees (
min_salary NUMBER
) RETURN emp_table_type
PIPELINED
IS
BEGIN
FOR rec IN (SELECT emp_id, emp_name, salary FROM employees WHERE salary > min_salary) LOOP
PIPE ROW (emp_record_type(rec.emp_id, rec.emp_name, rec.salary));
END LOOP;
RETURN;
END get_high_earning_employees;
/
How It Works:
The function takes a salary threshold (min_salary) as input.
It selects employees with salaries greater than this value.
The PIPE ROW statement sends each row one at a time to the calling SQL query.
Step 4: Query the Pipelined Function
Once the function is created, it can be used in SQL like a regular table.
SELECT * FROM TABLE(get_high_earning_employees(5000));
This query returns all employees earning more than 5000.
Step 5: Use the Function in APEX
In Oracle APEX, this function can be used in:
Interactive Reports: Create a report with SELECT * FROM TABLE(get_high_earning_employees(:P1_MIN_SALARY)).
Faceted Searches: Use an APEX item (e.g., P1_MIN_SALARY) to dynamically filter employees.
PL/SQL Processes: Fetch high-earning employees for business logic processing.
Performance Benefits of Pipelined Functions
