27.7 ADD_FILTER Procedures
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,
p_data_type IN t_data_type DEFAULT c_data_type_varchar2 );
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,
p_data_type IN t_data_type DEFAULT c_data_type_date );
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,
p_tokenize IN BOOLEAN DEFAULT NULL );
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 );
Signature 22
Note:
Requires Database 23ai and specific add_column
procedure:
apex_exec.add_vector_distance_column(
p_columns => l_columns );
PROCEDURE ADD_FILTER (
p_filters IN OUT NOCOPY t_filters,
p_vector_column_name IN VARCHAR2,
--
p_vector_search_type IN t_vector_search_type DEFAULT c_vector_search_exact,
p_distance_metric IN t_vector_distance_type DEFAULT c_vector_distance_euclidean,
--
-- only for p_vector_search_type = c_vector_search_approx
p_target_accuracy IN NUMBER DEFAULT NULL,
--
p_max_results IN NUMBER,
p_max_dist IN NUMBER DEFAULT NULL,
p_search_vector IN VECTOR );
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. |
p_vector_column_name |
Vector column to apply this filter on. |
p_vector_search_type |
Search Type. Use one of the t_vector_search_type constants.
|
p_distance_metric |
Distance Metric. Use one of the t_vector_distance_type constants.
|
p_target_accuracy |
Target accuracy. Only used if p_vector_search_type = c_vector_search_approx .
|
p_max_results |
Amount of rows to fetch. |
p_max_dist |
Maximum Vector Distance for the search results. |
p_search_vector |
Vector value for the Vector Search. |
p_data_type |
Data type of the column to apply this filter on. |
p_tokenize |
Whether to tokenize a row search term to individual words. |
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