How Do I Sort with apex_t_varchar2 in Oracle SQL in APEX

 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

Sorting Method	Best Use Case
Table Function + SQL Sorting	When integrating with SQL queries
Manual Bubble Sort	When working strictly within PL/SQL
In-memory Sorting	When performance is not critical

For APEX applications, using table functions is the best option, as it allows sorting within interactive reports, faceted searches, and other UI components.