Search Results igs_pe_dyn_sqlsegs_u1

Overview

IGS.IGS_PE_DYN_SQLSEGS is a transactional table in the Oracle E-Business Suite (EBS) IGS schema (the Student Systems / Oracle Student System product family), documented as VALID in both 12.1.1 and 12.2.2. The table's documented purpose is to "contain info of SQL segments from Discover." In practical terms, it stores large SQL statements in a decomposed, segmented form: rather than persisting a single monolithic SQL string, the statement is broken into ordered fragments (segments) that are written one row at a time. This design accommodates SQL text that may exceed practical single-column limits and supports streaming/piecewise write and read patterns.

From a Data Vault modeling perspective, the ETRM heuristic classifies this object as standalone — it has no foreign-key relationships to other database objects and is not referenced by any child objects. A standalone classification suggests a hub or reference-style entity rather than a link or satellite: the table is self-contained, and its natural business key should be modelled independently of surrounding transactional structures. Because the table carries full WHO audit columns, it can also be treated as an auditable descriptive snapshot keyed by its business key.

Key Information Stored

The table holds nine documented columns. The most significant are:

• SQLSEGS_ID (NUMBER) — the surrogate primary key, enforced by the unique index IGS_PE_DYN_SQLSEGS_PK. It uniquely identifies each SQL segment row and is the column applications use to reference an individual fragment.
• FILE_NAME (VARCHAR2(200)) — the file or source name associated with the stored SQL, typically the object being processed by the Discover extraction.
• SEG_SEQUENCE_NUM (NUMBER) — the ordinal position of the segment, indicating where the fragment falls within the reconstructed statement.
• SQL_SEGMENT (VARCHAR2(2000)) — the actual SQL text for that segment. Concatenating all segments in SEG_SEQUENCE_NUM order reconstitutes the full statement.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO audit columns recording insert and update metadata for each row.

Two unique indexes define the business-key candidates. IGS_PE_DYN_SQLSEGS_PK (SQLSEGS_ID) is the surrogate primary key. IGS_PE_DYN_SQLSEGS_U1 (FILE_NAME, SEG_SEQUENCE_NUM) is the composite business key: it enforces that, for a given file, each segment sequence number appears exactly once. This uniqueness constraint is the object that users encounter when searching for "igs_pe_dyn_sqlsegs_u1."

Common Use Cases and Queries

Typical uses include auditing stored dynamic SQL, reconstructing complete statements for troubleshooting, and validating segment continuity before replaying SQL against a target database.

• Retrieve all segments for a file in order: SELECT SEG_SEQUENCE_NUM, SQL_SEGMENT FROM IGS.IGS_PE_DYN_SQLSEGS WHERE FILE_NAME = :p_file ORDER BY SEG_SEQUENCE_NUM;
• Detect gaps or duplicates in the sequence, which would violate the _U1 business key.
• Join WHO audit columns against FND_USER to identify who loaded a given SQL file and when.
• Reporting on which files have been loaded, segment counts, and total stored text length per file.

Related Objects

The ETRM metadata records no FK dependencies in either direction: IGS_PE_DYN_SQLSEGS references no database object and is referenced by none. The APPS.IGS_PE_DYN_SQLSEGS synonym provides the APPS-layer access point used by concurrent programs and forms. Meaningful associations are therefore logical rather than enforced:

• APPS.IGS_PE_DYN_SQLSEGS — the APPS synonym resolving to this table for application code.
• IGS_PE_DYN_SQLSEGS_PK / IGS_PE_DYN_SQLSEGS_U1 — the primary and business-key unique indexes.
• FND_USER — joined via CREATED_BY / LAST_UPDATED_BY for audit reporting.

Because the object is standalone, joins should be constructed from the documented key columns rather than assumed foreign keys.