26.7 ADD_FILTER Procedure Signature 1

This procedure adds a filter to the filter collection.

Syntax

Signature 1

PROCEDURE ADD_FILTER (
    p_filters           IN OUT NOCOPY t_filters,
    p_filter_type       IN            t_filter_type,
    p_column_name       IN            t_column_name );

Signature 2

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            apex_t_varchar2,
    p_null_result       IN            BOOLEAN  DEFAULT FALSE,
    p_is_case_sensitive IN            BOOLEAN  DEFAULT TRUE );

Signature 3

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

Signature 4

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            apex_t_varchar2,
    p_null_result       IN            BOOLEAN DEFAULT FALSE,
    p_is_case_sensitive IN            BOOLEAN DEFAULT TRUE );

Signature 5

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

Signature 6

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

Signature 7

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            apex_t_number,
    p_null_result       IN            BOOLEAN DEFAULT FALSE );

Signature 8

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

Signature 9

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

Signature 10

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

Signature 11

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

Signature 12

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

Signature 13

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

Signature 14

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

Signature 15

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

Signature 16

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

Signature 17

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

Signature 18

PROCEDURE ADD_FILTER (
    p_filters           IN OUT NOCOPY t_filters,
    p_sql_expression    IN            VARCHAR2 );

Signature 19

Note:

This signature is only available if SDO_GEOMETRY (Oracle Locator) is installed in the database.

PROCEDURE ADD_FILTER (
    p_filters           IN OUT NOCOPY t_filters,
    p_filter_type       IN            t_filter_type,
    p_column_name       IN            VARCHAR2,
    p_value             IN            mdsys.sdo_geometry );

Signature 20

PROCEDURE ADD_FILTER (
    p_filters             IN OUT NOCOPY t_filters,
    p_search_index_owner  IN            VARCHAR2,
    p_search_index_table  IN            VARCHAR2,
    p_text_column_name    IN            VARCHAR2,
    p_text_query_function IN            VARCHAR2,
    p_value               IN            VARCHAR2 );

Signature 21

Note:

Use this signature for Oracle TEXT.

PROCEDURE ADD_FILTER (
    p_filters             IN OUT NOCOPY t_filters,
    p_text_column_name    IN            VARCHAR2,
    p_text_query_function IN            VARCHAR2,
    p_value               IN            VARCHAR2 );

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 (for example, equals or greater than).
p_values	Value array for IN or NOT IN filters.
p_from_value	Lower value for filters requiring a range (for example, between).
p_to_value	Upper value for filters requiring a range (for example, between).
p_interval	Interval for date filters (for example, 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.
p_search_columns	List of columns to apply the row search filter on.
p_text_column_name	Column name for the SQL contains expression when using Oracle TEXT or Ubiquitous Database Search.
p_text_query_function	Function to be used for the SQL contains expression when using Oracle TEXT or Ubiquitous Database Search.
p_search_index_owner	For Ubiquitous Database Search, to apply a filter for the Ubiquitous Search index source owner.
p_search_index_table	For Ubiquitous Database Search, to apply a filter for the Ubiquitous Search index source name.

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;