Creating and Using a Non-Pipelined Function in Oracle APEX
In Oracle APEX, functions play a vital role in processing data, performing calculations, and returning results for use in reports, validations, and dynamic computations. A non-pipelined function is a standard PL/SQL function that returns a single value or a collection (such as a table or record) without streaming results row-by-row like a pipelined function.
This tutorial explains how to create and use a non-pipelined function in Oracle APEX, providing examples and best practices.
What is a Non-Pipelined Function?
A non-pipelined function is a regular PL/SQL function that:
Processes data and returns a single value, a record, or a collection.
Does not use the PIPELINED keyword (which streams data row-by-row).
Can be used in SQL queries, PL/SQL processes, APEX computations, validations, and dynamic actions.
Creating a Non-Pipelined Function
A basic non-pipelined function returns a single value.
Example 1: Simple Function Returning a Value
This function calculates the total price of an order based on quantity and unit price.
CREATE OR REPLACE FUNCTION get_total_price (
p_quantity NUMBER,
p_unit_price NUMBER
) RETURN NUMBER IS
v_total NUMBER;
BEGIN
v_total := p_quantity * p_unit_price;
RETURN v_total;
END get_total_price;
/
How to Use in APEX
In a SQL Query (for Reports or Forms)
SELECT order_id, get_total_price(quantity, unit_price) AS total_price
FROM orders;
In PL/SQL Process (e.g., After Submit Button Click)
DECLARE
v_total NUMBER;
BEGIN
v_total := get_total_price(:P1_QUANTITY, :P1_UNIT_PRICE);
:P1_TOTAL := v_total; -- Assigning result to a page item
END;
In a Validation (To Ensure Price is Reasonable)
IF get_total_price(:P1_QUANTITY, :P1_UNIT_PRICE) > 1000 THEN
RETURN 'Total price cannot exceed 1000.';
END IF;
Returning a Record from a Non-Pipelined Function
A function can return a record type, such as customer details.
Example 2: Returning a Record (Single Row of Data)
CREATE OR REPLACE FUNCTION get_customer_info (
p_customer_id NUMBER
) RETURN customers%ROWTYPE IS
v_customer customers%ROWTYPE;
BEGIN
SELECT * INTO v_customer FROM customers WHERE customer_id = p_customer_id;
RETURN v_customer;
END get_customer_info;
/
How to Use in APEX
In a PL/SQL Process to Fill Page Items
DECLARE
v_customer customers%ROWTYPE;
BEGIN
v_customer := get_customer_info(:P1_CUSTOMER_ID);
:P1_CUSTOMER_NAME := v_customer.customer_name;
:P1_CUSTOMER_EMAIL := v_customer.email;
END;
Using in a Report
SELECT c.customer_id, c.customer_name, c.email
FROM customers c
WHERE c.customer_id = (SELECT customer_id FROM get_customer_info(:P1_CUSTOMER_ID));
Returning a Table from a Non-Pipelined Function
A function can return a table of data, which is useful for reports.
Example 3: Returning a Collection (Table Type Data)
Define a Table Type
CREATE OR REPLACE TYPE t_order_table AS TABLE OF orders%ROWTYPE;
/
Create the Function to Return the Collection
CREATE OR REPLACE FUNCTION get_orders_by_customer (
p_customer_id NUMBER
) RETURN t_order_table IS
v_orders t_order_table;
BEGIN
SELECT CAST(COLLECT(o) AS t_order_table)
INTO v_orders
FROM orders o
WHERE o.customer_id = p_customer_id;
RETURN v_orders;
END get_orders_by_customer;
/
How to Use in APEX
Using in SQL Query (For a Report Region)
SELECT * FROM TABLE(get_orders_by_customer(:P1_CUSTOMER_ID));
Using in a Process to Store Data in Collections
DECLARE
v_orders t_order_table;
BEGIN
v_orders := get_orders_by_customer(:P1_CUSTOMER_ID);
FOR i IN 1..v_orders.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Order ID: ' || v_orders(i).order_id);
END LOOP;
END;
Best Practices for Using Non-Pipelined Functions in APEX
Use Page Items as Parameters – When calling functions from APEX, pass page items (:P1_ITEM) as arguments.
Optimize for Performance – Avoid returning large datasets, as it can slow down reports.
Handle NULL Values – Ensure the function handles missing values gracefully.
Use Exception Handling – Prevent runtime errors by handling exceptions inside the function.
Example with Exception Handling:
CREATE OR REPLACE FUNCTION get_product_price (
p_product_id NUMBER
) RETURN NUMBER IS
v_price NUMBER;
BEGIN
SELECT price INTO v_price FROM products WHERE product_id = p_product_id;
RETURN v_price;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL; -- Return NULL if no product is found
WHEN OTHERS THEN
RETURN -1; -- Return -1 for unexpected errors
END get_product_price;
/
Non-pipelined functions are a powerful tool in Oracle APEX for performing calculations, retrieving records, and returning tables of data. They can be used in SQL queries, PL/SQL processes, reports, validations, and dynamic actions to enhance application functionality.
