Results for “icx_text”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

ICX_TEXT is a table owned by the ICX schema, the database account that underpins Oracle iProcurement. The ETRM metadata describes its purpose succinctly as a "parameter encryption table." In practical terms, ICX_TEXT is a transient store for encoded text values that iProcurement writes and reads while managing session-scoped parameters, most notably values that must be obscured on disk rather than persisted in clear text. Each row associates a body of text material with a specific iProcurement session, allowing the application to round-trip sensitive or internally generated parameter content without exposing it directly in persistent application tables.

From a data-modeling perspective, the metadata's heuristic Data Vault classification is satellite-leaning. That is a modeling suggestion rather than a statement of Oracle's design intent: ICX_TEXT behaves like a descriptive satellite hanging off a parent hub or link, because its rows exist to describe context attached to something else — here, the session identified by SESSION_ID. The table carries its own surrogate key but derives most of its meaning from the parent session relationship, which is characteristic of satellite-style attribution rather than an independent business entity.

Key Information Stored

The documented physical schema for this table in ETRM 12.2.2 consists of four columns, and only these should be relied upon:

• TEXT_ID — The surrogate primary key, enforced by the ICX_TEXT_PK constraint. It is also the single column of unique index ICX_TEXT_U1, which the metadata identifies as the business-key candidate. In practice TEXT_ID is a system-generated identifier that uniquely distinguishes each parameter text row.
• SESSION_ID — The foreign key column referencing ICX_SESSIONS. This is the join path back to the originating iProcurement session and is the principal access path for retrieving text belonging to a given user interaction.
• TEXT — The payload column holding the parameter content itself. Consistent with the table's stated role, this is where the encoded or encrypted text value is stored.
• TIMESTAMP — The temporal attribute, typically capturing when the row was written. It supports age-based cleanup of stale parameter rows and chronological ordering where multiple rows exist for one session.

The metadata does not document additional descriptive columns; because the table is narrow and session-bound, its usefulness comes from the TEXT and TIMESTAMP pairing rather than from breadth of attributes.

Common Use Cases and Queries

ICX_TEXT is primarily an internal support table. It is worked with indirectly by iProcurement internals, but DBAs and technical consultants query it when diagnosing session parameter problems, verifying that text was actually written, or purging orphaned rows.

• Checking whether a session has associated text rows: SELECT TEXT_ID, TIMESTAMP FROM ICX_TEXT WHERE SESSION_ID = :session_id ORDER BY TIMESTAMP;
• Verifying row volume and recency per session to detect accumulation: SELECT SESSION_ID, COUNT(*), MAX(TIMESTAMP) FROM ICX_TEXT GROUP BY SESSION_ID;
• Integrity checks for rows that no longer map to a live session, using a left outer join to ICX_SESSIONS and filtering for NULL on the session side.
• Age-based housekeeping, deleting rows beyond a retention threshold based on TIMESTAMP, coordinated with the application's session-expiry policy.

Because the TEXT column may contain encoded content, reporting against its literal values is rarely meaningful; the practical reporting use cases concern counts, timestamps, and session association rather than the decoded parameter values themselves.

Related Objects

The metadata documents both directions of referential linkage around ICX_TEXT:

• ICX_SESSIONS — The parent table, joined via ICX_TEXT.SESSION_ID = ICX_SESSIONS.SESSION_ID. This is the primary relationship and the most significant dependency, since it anchors every text row to an iProcurement session.
• GML_GASNO_DETAIL_TEXT — A child table whose TEXT_ID foreign key references ICX_TEXT, linking process-manufacturing detail text back to this parameter store.
• GML_GPOAO_DETAIL_TEXT — A second child table, likewise joined via TEXT_ID, performing a comparable referencing role for its own detail-text records.
• ICX_TEXT_PK / ICX_TEXT_U1 — The primary key constraint and unique index that define and enforce TEXT_ID uniqueness, and which any population logic must respect.

These relationships confirm that ICX_TEXT sits between session management and downstream detail-text consumers, and that TEXT_ID is the durable identifier relied upon by both referencing tables.