25.7 ADD_ORDER_BY Procedure

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

Syntax

procedure 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 );

procedure 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

Table 25-3 ADD_ORDER_BY Procedure 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 should be sorted ascending or descending. Valid values are c_order_asc and c_order_desc.

p_order_nulls

Defines if NULL data will sort 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;