14.4 ADD_FILTER Procedure

This procedure adds a filter to the filter collection.

Syntax

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_value             in            varchar2,
    p_null_result       in            boolean  default false,
    p_is_case_sensitive in            boolean  default true );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_from_value        in            varchar2,
    p_to_value          in            varchar2,
    p_null_result       in            boolean default false,
    p_is_case_sensitive in            boolean default true );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_values            in            wwv_flow_t_varchar2,
    p_null_result       in            boolean default false,
    p_is_case_sensitive in            boolean default true );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_value             in            number,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_from_value        in            number,
    p_to_value          in            number,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_values            in            wwv_flow_t_number,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_value             in            date,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_from_value        in            date,
    p_to_value          in            date,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_value             in            timestamp,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_from_value        in            timestamp,
    p_to_value          in            timestamp,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_value             in            timestamp with time zone,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_from_value        in            timestamp with time zone,
    p_to_value          in            timestamp with time zone,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_value             in            timestamp with local time zone,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_from_value        in            timestamp with local time zone,
    p_to_value          in            timestamp with local time zone,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_filter_type       in            t_filter_type,
    p_column_name       in            t_column_name,
    p_interval          in            pls_integer,
    p_interval_type     in            t_filter_interval_type,
    p_null_result       in            boolean default false );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_search_columns    in            t_columns,
    p_is_case_sensitive in            boolean default false,
    p_value             in            varchar2 );

procedure add_filter (
    p_filters           in out nocopy t_filters,
    p_sql_expression    in            varchar2 );

Parameters

Table 14-2 ADD_FILTER Procedure Parameters

Parameter	Description
`p_filters`	Filters array.
`p_filter_type`	Type of filter - use one of the `t_filter_type` constants.
`p_column_name`	Column to apply this filter on.
`p_value`	Value for filters requiring one value (e.g. equals or greater than).
`p_values`	Value array for `IN` or `NOT IN` filters.
`p_from_value`	Lower value for filters requiring a range (e.g. between).
`p_to_value`	Upper value for filters requiring a range (e.g. between).
`p_interval`	Interval for date filters (e.g. last X months).
`p_interval_type`	Interval type for date filters (months, dates).
`p_sql_expression`	Generic SQL expression to use as filter.
`p_null_result`	Result to return when the actual column value is `NULL`.
`p_is_case_sensitive`	Whether this filter should work case-sensitive or not.

Example

declare
    l_filters     apex_exec.t_filters;
    l_context     apex_exec.t_context;
begin
    apex_exec.add_filter(
        p_filters     => l_filters,
        p_filter_type => apex_exec.c_filter_eq,
        p_column_name => 'ENAME',
        p_value       => 'KING' );

   apex_exec.add_filter(
       p_filters     => l_filters,
       p_filter_type => apex_exec.c_filter_gt,
       p_column_name => 'SAL',
       p_value       => 2000 );

   l_context := apex_exec.open_web_source_query(
       p_module_static_id => '{web source module static ID}', 
       p_filters          => l_filters
       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;

Parent topic: APEX_EXEC