Search Results igs_pe_dyn_sqlsegs




Overview
Explore in AI Chat

Overview

The IGS.IGS_PE_DYN_SQLSEGS table is a core repository object within the Oracle E-Business Suite Student System (IGS) product family. Its documented purpose is to store information about SQL segments originating from "Discover," the legacy Oracle Student System component historically used for ad hoc reporting against student data. In practical terms, each row captures one discrete fragment of a dynamically constructed SQL statement, keyed to the file or report definition that owns it. This design allows the application to persist large SQL statements in ordered, manageable pieces rather than as a single oversized column.

The object resides in the IGS schema and carries a VALID status in the documented release (12.1.1), with the same physical definition remaining valid under 12.2.2. From a Data Vault modeling perspective, the heuristic classification mined from the FK structure is standalone. This is a modeling suggestion only: no foreign key relationships were mined, meaning the table is not naturally a hub, link, or satellite in the strict Data Vault sense, and it may be treated as an independent reference or staging object within a warehouse design.

Key Information Stored

The table contains nine documented columns. The most significant are summarized below.

  • SQLSEGS_ID — The surrogate primary key, enforced by the unique index IGS_PE_DYN_SQLSEGS_PK. This is a system-generated identifier with no business meaning.
  • FILE_NAME — Identifies the Discover file or report definition to which the SQL segment belongs. Along with SEG_SEQUENCE_NUM, it forms a business-key candidate via the unique index IGS_PE_DYN_SQLSEGS_U1.
  • SEG_SEQUENCE_NUM — The ordinal position of the segment within its parent SQL statement, so that fragments can be retrieved and reassembled in the correct order.
  • SQL_SEGMENT — The actual text fragment of the dynamically generated SQL statement.
  • CREATED_BY, CREATION_DATE — Standard WHO columns recording the user and timestamp of row insertion.
  • LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns capturing the most recent modification, its timestamp, and the login session that performed it.

The distinction between the surrogate key (SQLSEGS_ID) and the composite business key (FILE_NAME, SEG_SEQUENCE_NUM) is important for deduplication and ETL logic. Note that the documented schema does not include an explicit organization or business-group column, so multi-org filtering is not applicable to this table.

Common Use Cases and Queries

The primary administrative and analytical use case is reconstructing the full SQL text associated with a Discover file so that report logic can be audited, debugged, or migrated. Segments must always be ordered by SEG_SEQUENCE_NUM.

  • Retrieving all segments for a given file: SELECT SEG_SEQUENCE_NUM, SQL_SEGMENT FROM IGS_PE_DYN_SQLSEGS WHERE FILE_NAME = :p_file ORDER BY SEG_SEQUENCE_NUM;
  • Identifying files with the most segments, useful for performance analysis of legacy Discover reports.
  • Auditing recent changes: filter on LAST_UPDATE_DATE or LAST_UPDATED_BY to trace who modified a report definition.
  • Impact analysis prior to decommissioning Discover, by enumerating distinct FILE_NAME values that still hold stored SQL.

Because SQL_SEGMENT may hold long text, reports should avoid concatenating full statements in high-volume queries and instead operate at the segment or file level.

Related Objects

The mined relationship data classifies IGS_PE_DYN_SQLSEGS as standalone, so no documented foreign keys point to or from it. Consequently, no authoritative join columns beyond its own key are available from the metadata. In practice, the most meaningful association is the logical link between FILE_NAME and the parent Discover report or file definition that owns the segments; this association exists at the application level rather than through an enforced FK constraint.

  • IGS_PE_DYN_SQLSEGS_PK — unique index on SQLSEGS_ID (primary key).
  • IGS_PE_DYN_SQLSEGS_U1 — unique index on (FILE_NAME, SEG_SEQUENCE_NUM), the business-key candidate.

Any additional related tables, views, or public APIs that reference this object should be confirmed directly in the target instance, since the ETRM metadata does not document enforced dependencies for this standalone table.