ADD_ORDER_BY Procedure

This procedure adds an order by expression to the order bys collection.

Syntax

Signature 1

APEX_EXEC.ADD_ORDER_BY (
    p_order_bys         IN OUT NOCOPY t_order_bys,
    p_position          IN            PLS_INTEGER,
    p_direction         IN            t_order_direction DEFAULT c_order_asc,
    p_order_nulls       IN            t_order_nulls     DEFAULT NULL )

Signature 2

APEX_EXEC.ADD_ORDER_BY (
    p_order_bys         IN OUT NOCOPY t_order_bys,
    p_column_name       IN            t_column_name,
    p_direction         IN            t_order_direction DEFAULT c_order_asc,
    p_order_nulls       IN            t_order_nulls     DEFAULT NULL )

Parameters

Parameter	Description
`p_order_bys`	Order by collection.
`p_position`	References a column of the provided data source by position.
`p_column_name`	References a column name or alias of the provided data source.
`p_direction`	Defines if the column is sorted ascending or descending. Valid values are `c_order_asc` and `c_order_desc`.
`p_order_nulls`	Defines if NULL data sorts to the bottom or top. Valid values are `NULL`, `c_order_nulls_first` and `c_order_nulls_last`. Use `NULL` for automatic handling based on the sort direction.

Example

DECLARE
    l_order_bys   apex_exec.t_order_bys;
    l_context     apex_exec.t_context;
BEGIN
    apex_exec.add_order_by(
         p_order_bys     => l_order_bys,
         p_column_name   => 'ENAME',
         p_direction     => apex_exec.c_order_asc );

    l_context := apex_exec.open_web_source_query(
        p_module_static_id => '{web source module static ID}',
        p_order_bys        => l_order_bys,
        p_max_rows         => 1000 );

        WHILE apex_exec.next_row( l_context ) LOOP
            -- process rows here ...
        END LOOP;

    apex_exec.close( l_context );
    EXCEPTION
        WHEN others THEN
            apex_exec.close( l_context );
    RAISE;
END;