Search Results sql_segment




Overview
Explore in AI Chat

Overview

IGS.IGS_PE_DYNAMIC_SQL is a persistent table within the Oracle E-Business Suite (EBS) IGS (Student Systems / Student Information) schema. It stores dynamically generated SQL statement fragments—termed "SQL segments"—that are produced by Oracle Discoverer workbooks and subsequently consumed by Oracle's student information processing logic. The table's documentation explicitly notes that SQL_SEGMENT holds a "Static SQL Statement (Segment Value) from Discoverer," indicating the object serves as a repository for pre-built SQL building blocks assembled by an external reporting tool rather than an OLTP transactional entity.

From a Data Vault modeling perspective, the metadata's heuristic classification is satellite-leaning. This suggests the table behaves as a descriptive satellite attached to a parent grouping entity (IGS_PE_DYNAMIC_PERSID via GROUP_ID), where the SQL_SEGMENT content is the descriptive payload and GROUP_ID plus SEGMENT_SEQUENCE together form the business key context. This classification is a modeling suggestion derived from the FK topology, not an authoritative EBS design declaration.

The table resides in the APPS_TS_ARCHIVE tablespace with PCTFREE 10, consistent with archival or semi-static reference data rather than high-volatility transaction storage. Status is documented as VALID in both 12.1.1 and 12.2.2 ETRM repositories.

Key Information Stored

The table comprises eight documented columns. The most significant are:

  • GROUP_ID (NUMBER) — The grouping identifier. It is both a mandatory component of the composite primary key and a foreign key referencing IGS_PE_DYNAMIC_PERSID.GROUP_ID. It links each SQL segment to its parent dynamic-personalization record.
  • SEGMENT_SEQUENCE (NUMBER, 15) — The SQL segment number, forming the second half of the composite unique key. It orders or distinguishes multiple SQL segments belonging to the same group.
  • SQL_SEGMENT (VARCHAR2, 2000) — The substantive payload: a static SQL statement fragment originating from Discoverer. This is the column targeted by the user's "sql_segment" search term.
  • CREATED_BY, CREATION_DATE — Standard "Who" audit columns capturing the inserting user and timestamp.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard "Who" audit columns capturing the most recent modifying user, timestamp, and login context.

The surrogate primary key is IGS_PE_DYNAMIC_SQL_PK (GROUP_ID, SEGMENT_SEQUENCE), which is also the single documented unique index and therefore the business-key candidate. No separate single-column surrogate (such as a numeric SQL_ID) is documented; identity is expressed entirely through the group-plus-sequence pairing.

Common Use Cases and Queries

Typical usage involves retrieving the ordered set of SQL fragments for a given group, or searching for fragments that reference a particular column or object. A standard retrieval pattern is:

  • SELECT SEGMENT_SEQUENCE, SQL_SEGMENT FROM IGS.IGS_PE_DYNAMIC_SQL WHERE GROUP_ID = :group_id ORDER BY SEGMENT_SEQUENCE;
  • Searching text fragments: SELECT GROUP_ID, SEGMENT_SEQUENCE, SQL_SEGMENT FROM IGS.IGS_PE_DYNAMIC_SQL WHERE UPPER(SQL_SEGMENT) LIKE '%SQL_SEGMENT%';

Reporting scenarios include auditing which Discoverer-derived statements remain cached, identifying stale fragments by CREATION_DATE or LAST_UPDATE_DATE, and joining back to IGS_PE_DYNAMIC_PERSID to correlate a SQL segment with the personalization record it serves. Because SQL_SEGMENT is a 2000-character VARCHAR2, string concatenation may be required when a logical statement spans multiple segment rows.

Related Objects

  • IGS.IGS_PE_DYNAMIC_PERSID — The parent table referenced by the GROUP_ID foreign key; joining on GROUP_ID = IGS_PE_DYNAMIC_PERSID.GROUP_ID links each SQL segment to its owning personalization identity.
  • APPS schema synonym IGS_PE_DYNAMIC_SQL — The APPS-owned synonym through which EBS application code and reports reference this table without schema qualification.
  • Discoverer workbooks and EUL (End User Layer) objects — the upstream source that populates SQL_SEGMENT, though not documented as a formal database dependency.
  • IGS student processing packages that read the assembled SQL segments — downstream consumers of the cached statements.

The documented dependency data confirms that IGS_PE_DYNAMIC_SQL references no database object other than its GROUP_ID parent, and is itself referenced within the APPS layer via the synonym. No inverse child tables are listed, reinforcing its satellite role attached to the dynamic personalization record.