Search Results icx_context_results_temp




Overview
Explore in AI Chat

Overview

ICX.ICX_CONTEXT_RESULTS_TEMP is a temporary staging table in the Oracle iProcurement (ICX) module of Oracle E-Business Suite, available in releases 12.1.1 and 12.2.2. As its description indicates — "ConText queries table" — the object serves as a transient repository for the results of Oracle Text (interMedia ConText) search operations executed against the iProcurement catalog and related text-indexed content. When a user performs a keyword or natural-language search in iProcurement, the ConText engine evaluates the query against text indexes and writes the resulting matches, along with their relevance scores, into this table for subsequent consumption by application logic and display.

The table is owned by the ICX schema and is documented as VALID in ETRM 12.2.2. Under the heuristic Data Vault classification derived from its foreign-key structure, ICX_CONTEXT_RESULTS_TEMP is modeled as a standalone table — it exposes no meaningful outbound foreign-key relationships and is best treated as an isolated staging or work area rather than a hub, link, or satellite. This is consistent with its role as a short-lived, session-oriented results buffer rather than a transactional master or reference entity.

Key Information Stored

The documented physical schema comprises five columns, with a composite primary key and a matching unique index that together define the object's grain.

  • CONID — the ConText query or context identifier. Part of the composite primary key (ICX_CONTEXT_RESULTS_TEMP_PK) and the unique index (ICX_CONTEXT_RESULTS_TEMP_U1). It associates each result row with a specific search execution.
  • TEXTKEY — the primary text key identifying the matched document or catalog item. Also part of both the PK and U1 unique index.
  • TEXTKEY2 — a secondary text key, providing finer-grained identification where the primary key alone is insufficient. The third component of the composite PK and unique index.
  • SCORE — the relevance score assigned by the ConText engine, reflecting how well the matched item satisfies the query. Used to rank and order results for presentation.
  • DATESTAMP — the timestamp recording when the result row was generated, supporting purge logic and result freshness evaluation.

The composite primary key (CONID, TEXTKEY, TEXTKEY2) establishes the surrogate/business-key candidate, with the unique index ICX_CONTEXT_RESULTS_TEMP_U1 on the same column triple enforcing uniqueness. The SCORE and DATESTAMP columns carry the descriptive attributes, giving the table a characteristic lightweight key-plus-attribute shape.

Common Use Cases and Queries

Because the object functions as a temporary results cache, typical interactions are retrieval, ranking, and cleanup rather than long-term persistence.

  • Retrieving ranked results for a specific search context: SELECT TEXTKEY, TEXTKEY2, SCORE, DATESTAMP FROM ICX.ICX_CONTEXT_RESULTS_TEMP WHERE CONID = :p_conid ORDER BY SCORE DESC;
  • Identifying matched items below a relevance threshold: SELECT TEXTKEY FROM ICX.ICX_CONTEXT_RESULTS_TEMP WHERE CONID = :p_conid AND SCORE < :p_min_score;
  • Assessing result-set size per query: SELECT CONID, COUNT(*) FROM ICX.ICX_CONTEXT_RESULTS_TEMP GROUP BY CONID;
  • Purging stale rows: DELETE FROM ICX.ICX_CONTEXT_RESULTS_TEMP WHERE DATESTAMP < SYSDATE - :p_retention_days;

Reporting use cases center on search-quality analysis — measuring score distributions, match counts, and search performance — while DBA routines rely on the DATESTAMP column to manage table growth, since temporary accumulation of stale rows can affect search responsiveness.

Related Objects

Given the documented heuristic classification as standalone, direct foreign-key dependencies are not surfaced. Related objects are best inferred from the shared ICX schema and the ConText search flow:

  • ICX_CAT_ITEMS_CTX and other iProcurement catalog text-index tables — provide the indexed content whose matches populate TEXTKEY/TEXTKEY2.
  • ICX_CAT_SEARCH_RESULTS (or equivalent search-result staging structures) — downstream consumers of ranked output.
  • ICX_CAT_CATALOG_ITEMS — resolves the text keys back to concrete catalog entries.
  • ICX_POR_CATALOG_ITEMS — the punchout/catalog item detail commonly joined during result enrichment.
  • CTX_* views and the CTXSYS schema — the Oracle Text engine objects that generate scores written to SCORE.

Because no FK constraints are documented, joins to these objects rely on application-level key matching via TEXTKEY, TEXTKEY2, and CONID rather than enforced referential integrity. This reinforces the object's role as a transient, service-oriented staging area within the iProcurement search architecture.