apex_t_varchar2 is a built-in PL/SQL table type used in Oracle APEX to store and process arrays of strings. It is commonly used when handling collections, multi-value parameters, or returning datasets from functions. However, since it is an associative array, it does not have built-in sorting mechanisms like SQL queries do.
This tutorial will show different methods to sort an apex_t_varchar2 array in Oracle SQL within an APEX application.
Creating and Using apex_t_varchar2
Declaring and Populating an apex_t_varchar2 Array
In PL/SQL, you can declare an apex_t_varchar2 array and manually insert values into it.
DECLARE
l_names apex_t_varchar2 := apex_t_varchar2();
BEGIN
l_names.EXTEND(5);
l_names(1) := 'Charlie';
l_names(2) := 'Alice';
l_names(3) := 'Eve';
l_names(4) := 'Bob';
l_names(5) := 'David';
END;
/
This creates an unsorted array with five values.
Sorting apex_t_varchar2 Using SQL Table Functions
Since apex_t_varchar2 is a PL/SQL collection, it does not support ORDER BY directly in SQL. However, we can use table functions to convert the collection into a table and apply sorting.
Step 1: Create a Table Function
A table function is a PL/SQL function that returns a table-like structure that SQL queries can process.
CREATE OR REPLACE FUNCTION sort_varchar2_array (
p_array apex_t_varchar2
) RETURN TABLE OF VARCHAR2(4000)
PIPELINED
IS
BEGIN
FOR i IN 1 .. p_array.COUNT LOOP
PIPE ROW (p_array(i));
END LOOP;
RETURN;
END sort_varchar2_array;
/
This function iterates over the array and returns each row.
The PIPELINED keyword allows the function to be used in SQL queries.
Step 2: Use the Function to Sort
Now, you can call the function in an SQL query and sort the output.
DECLARE
l_names apex_t_varchar2 := apex_t_varchar2();
BEGIN
l_names.EXTEND(5);
l_names(1) := 'Charlie';
l_names(2) := 'Alice';
l_names(3) := 'Eve';
l_names(4) := 'Bob';
l_names(5) := 'David';
-- Query the function and apply sorting
FOR rec IN (SELECT COLUMN_VALUE AS name
FROM TABLE(sort_varchar2_array(l_names))
ORDER BY name)
LOOP
DBMS_OUTPUT.PUT_LINE(rec.name);
END LOOP;
END;
/
This will output:
Alice
Bob
Charlie
David
Eve
The names are now sorted alphabetically.
Sorting apex_t_varchar2 Manually Using PL/SQL
If you need to sort the array within PL/SQL without converting it into a table, you can use a nested loop sorting algorithm like Bubble Sort or QuickSort.
Example: Bubble Sort in PL/SQL
DECLARE
l_names apex_t_varchar2 := apex_t_varchar2();
temp VARCHAR2(100);
BEGIN
l_names.EXTEND(5);
l_names(1) := 'Charlie';
l_names(2) := 'Alice';
l_names(3) := 'Eve';
l_names(4) := 'Bob';
l_names(5) := 'David';
-- Bubble Sort Algorithm
FOR i IN 1 .. l_names.COUNT LOOP
FOR j IN 1 .. l_names.COUNT - 1 LOOP
IF l_names(j) > l_names(j + 1) THEN
temp := l_names(j);
l_names(j) := l_names(j + 1);
l_names(j + 1) := temp;
END IF;
END LOOP;
END LOOP;
-- Display Sorted Results
FOR i IN 1 .. l_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_names(i));
END LOOP;
END;
/
This method is slower but works without a table function.
When to Use These Sorting Methods
For APEX applications, using table functions is the best option, as it allows sorting within interactive reports, faceted searches, and other UI components.
