Creating and Using a Pipelined Function in Oracle APEX
A pipelined function in Oracle is a special type of function that returns a table of data row-by-row instead of all at once. This improves performance and memory efficiency, especially for large datasets.
Pipelined functions are commonly used in reports, data processing, and transformations in Oracle APEX. This tutorial covers how to create and use a pipelined function effectively.
What is a Pipelined Function?
A pipelined function processes and returns data incrementally as it is being generated. It does this by using the PIPELINED keyword and PIPE ROW statements to return each row dynamically.
Advantages of Pipelined Functions
Improved Performance – Processes rows one at a time instead of loading all data into memory.
Efficient for Large Datasets – Works well with millions of rows by streaming results.
Seamless Integration – Can be used in SQL queries, reports, and APIs.
Steps to Create a Pipelined Function
Step 1: Define a Table Type
Before creating the function, define a table type that represents the structure of the data being returned.
CREATE OR REPLACE TYPE t_employee AS OBJECT (
emp_id NUMBER,
emp_name VARCHAR2(100),
emp_salary NUMBER
);
/
CREATE OR REPLACE TYPE t_employee_table AS TABLE OF t_employee;
/
This defines:
t_employee – An object representing an employee with ID, name, and salary.
t_employee_table – A collection (table) type to hold multiple employees.
Step 2: Create the Pipelined Function
The function retrieves employees and returns the data row by row.
CREATE OR REPLACE FUNCTION get_high_salary_employees (
p_min_salary NUMBER
) RETURN t_employee_table PIPELINED IS
BEGIN
FOR emp_rec IN (
SELECT emp_id, emp_name, emp_salary
FROM employees
WHERE emp_salary > p_min_salary
) LOOP
PIPE ROW (t_employee(emp_rec.emp_id, emp_rec.emp_name, emp_rec.emp_salary));
END LOOP;
RETURN;
END get_high_salary_employees;
/
How This Function Works
Takes an input parameter (p_min_salary) to filter employees based on salary.
Loops through the employee records that meet the condition.
Uses PIPE ROW to return each row immediately instead of waiting for all data to be processed.
Ends with RETURN; to complete execution.
Using the Pipelined Function in APEX
1. Using the Function in a SQL Query for Reports
You can call the function inside a SELECT statement to retrieve employees dynamically.
SELECT * FROM TABLE(get_high_salary_employees(5000));
This returns all employees with a salary greater than 5000 and can be used in an Interactive Report or Classic Report in Oracle APEX.
2. Creating a Report in APEX with a Pipelined Function
Go to APEX Page Designer
Add a Classic or Interactive Report
Set the SQL Query as:
SELECT * FROM TABLE(get_high_salary_employees(:P1_MIN_SALARY));
Create a page item (P1_MIN_SALARY) to allow users to input the minimum salary.
Run the page and enter a value in P1_MIN_SALARY to filter results dynamically.
3. Using the Function in a PL/SQL Process
Pipelined functions can also be used in PL/SQL processes inside APEX.
DECLARE
v_employee_list t_employee_table;
BEGIN
v_employee_list := get_high_salary_employees(:P1_MIN_SALARY);
FOR i IN 1..v_employee_list.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_list(i).emp_name || ', Salary: ' || v_employee_list(i).emp_salary);
END LOOP;
END;
This PL/SQL block calls the function and loops through the returned employees.
4. Using a Pipelined Function in a RESTful Web Service
Oracle APEX supports RESTful Web Services, which can return pipelined function data as JSON.
Create a RESTful Web Service in APEX
Set the SQL Query as:
SELECT * FROM TABLE(get_high_salary_employees(:P1_MIN_SALARY));
Expose the service to return data in JSON format.
Example JSON response:
[
{ "EMP_ID": 101, "EMP_NAME": "Alice", "EMP_SALARY": 6000 },
{ "EMP_ID": 102, "EMP_NAME": "Bob", "EMP_SALARY": 7000 }
]
Best Practices for Using Pipelined Functions in APEX
Use Appropriate Data Types – Ensure the table type matches the expected data structure.
Optimize Queries – Add indexes and use efficient filtering to improve performance.
Handle Exceptions – Prevent runtime errors by using BEGIN...EXCEPTION...END.
Example with Exception Handling
CREATE OR REPLACE FUNCTION get_high_salary_employees_safe (
p_min_salary NUMBER
) RETURN t_employee_table PIPELINED IS
BEGIN
FOR emp_rec IN (
SELECT emp_id, emp_name, emp_salary FROM employees
WHERE emp_salary > p_min_salary
) LOOP
PIPE ROW (t_employee(emp_rec.emp_id, emp_rec.emp_name, emp_rec.emp_salary));
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employees found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error.');
END get_high_salary_employees_safe;
/
This ensures the function does not fail unexpectedly if no records are found or if an error occurs.
Pipelined functions in Oracle APEX allow efficient data retrieval row by row, improving performance and usability in reports, PL/SQL processes, and RESTful services.
