SEARCH Function

This function performs application search.

Syntax

APEX_SEARCH.SEARCH (
    p_search_static_ids      IN apex_t_varchar2,
    p_search_expression      IN VARCHAR2,
    p_apply_order_bys        IN VARCHAR2           DEFAULT 'Y',
    --
    p_return_total_row_count IN VARCHAR2           DEFAULT 'N' )
    RETURN apex_search_result_table pipelined;

Parameters

Parameter	Description
`p_search_static_ids`	List of Search Configuration Static IDs to search within.
`p_search_expression`	Terms to use in the search.
`p_apply_order_bys`	Whether to apply the sort settings defined in the search configuration. Pass `N` in when the query applies its own `ORDER BY` clause.
`p_return_total_row_count`	Whether to return the total row count.

Returns

This function returns a table of search results as defined by the apex_search_result_table object type. The following columns are available:

CONFIG_LABEL:         Label of the search configuration this result comes from.
RESULT_SEQ:           Sequence of this result within the search configuration.

The following column contents are based on the mapping within the Search Configuration:

PRIMARY_KEY_1:        Primary Key Column 1
PRIMARY_KEY_2:        Primary Key Column 2
TITLE:                Title
SUBTITLE:             Subtitle
DESCRIPTION:          Description
BADGE:                Value to be shown as result "badge"
LAST_MODIFIED:        Timestamp when the result was last modified.
CUSTOM_01:            Custom attribute 1
CUSTOM_02:            Custom attribute 2
CUSTOM_03:            Custom attribute 3
SCORE:                Score or Rank value. If Oracle TEXT is used, the TEXT Score is returned.
LINK:                 Link
RESULT_CSS_CLASSES:   Result CSS Classes

FORMATTED_ROW:        Row HTML, if a row template is specified in the search configuration

ICON_TYPE:            Type of the Icon: CLASS, URL, BLOB or INITIALS
ICON_VALUE:           Icon Value, depending on the ICON TYPE
ICON_BLOB:            BLOB containing the icon
ICON_MIMETYPE:        Mimetype of the icon BLOB, if configured

TOTAL_ROW_COUNT:      Total result count, if configured.
CONFIG_ID:            Internal ID of the search configuration this result comes from.

Example

The following example searches for "oracle APEX" within the CUSTOMERS and PRODUCTS search configuration.

select config_label, title, subtitle, badge
    from table( apex_search.search(
                   p_search_static_ids => apex_t_varchar2( 'PRODUCTS', 'CUSTOMERS' ),
                   p_search_expression => 'oracle APEX',
                   p_apply_order_bys   => 'N' ) );

CONFIG_LABEL   TITLE              SUBTITLE                BADGE
-------------- ------------------ ----------------------- -------
Products       APEX vacation app  Subscription Based App 
Products       APEX time entry    On-Premises License

:

Customers      John Doe Corp      Software Development    5000
Customers      Development Corp   Software Development    1000

: