Search Results create_or_regenerate_index




Overview
Explore in AI Chat

Overview

QA_CHAR_INDEXES_PKG is a VALID PL/SQL package owned by the APPS schema in Oracle EBS 12.1.1 and 12.2.2. It is classified as an OTHER API under the Oracle E-Business Suite quality management modules. Its principal business function is the dynamic management of database indexes that support character-based (qualitative) specification results within Oracle Quality. In quality plans, characteristics may be validated against lists of permitted values, and these value sets frequently reside in external tables. The package builds and maintains function-based indexes, predicate expressions, and DECODE/CASE expressions over those value columns so that Quality validation queries against characteristic values remain performant. Because the underlying value tables vary from customer to customer, the package relies on dynamic SQL generation rather than static index definitions, which accounts for its dependency on DBMS_SQL and the supporting data dictionary views.

Key Procedures and Functions

The package exposes fourteen documented procedures and functions. Their purposes are as follows:

  • WRAPPER — General-purpose entry point that encapsulates the core logic of index construction and evaluation.
  • GET_PREDICATE — Returns the predicate text used to locate or qualify rows in the character index context.
  • INDEX_EXISTS — Determines whether a required index already exists on the target value table.
  • INDEX_EXISTS_AND_ENABLED — Extends the existence check to confirm the index is present and currently enabled and usable.
  • GET_DEFAULT_RESULT_COLUMN — Retrieves the default result column name associated with a characteristic or plan, used when building the index expression.
  • DISABLE_INDEX — Disables an existing character index, typically during maintenance or redefinition.
  • DROP_INDEX — Removes an existing character index from the database.
  • CREATE_OR_REGENERATE_INDEX — Creates the function-based index when absent, or rebuilds it when the structure is stale.
  • INSERT_ROW — Inserts a row describing a character index into the package's metadata repository.
  • DELETE_ROW — Deletes a corresponding metadata row when an index is dropped or retired.
  • GET_INDEX_PREDICATE — Returns the WHERE-clause predicate associated with a stored index definition.
  • GET_DECODE_FUNCTION — Returns a DECODE-based function expression for translating characteristic values.
  • CONSTRUCT_DECODE_FUNCTION — Dynamically assembles a DECODE function body from characteristic value/result pairs.
  • GET_CASE_FUNCTION — Returns an equivalent CASE-based function expression for value translation.

Tables Accessed

Access is performed through APPS synonyms. QA_CHAR_INDEXES is the package's own metadata table, holding the definitions of created indexes; INSERT_ROW and DELETE_ROW maintain it. QA_PLANS and QA_PLAN_CHARS supply plan and characteristic definitions, so predicates and result columns can be derived. AD_DDL is used to submit DDL operations such as CREATE INDEX and DROP INDEX through the standard EBS concurrent mechanism. ALL_INDEXES is queried to verify index existence and status. DBMS_SQL, DBMS_LOB, and PLITBLM are utility packages used to parse, execute, and store dynamically generated SQL and large expression text.

Usage Notes

QA_CHAR_INDEXES_PKG is invoked indirectly rather than from end-user forms. It is called by eight dependent packages, including QA_CHAR_UPDATE_PKG, QA_PARENT_CHILD_COPY_PKG, QA_PERFORMANCE_PUB, QA_PLANS_PUB, and the library units QLTAUFLB and QLTCPPLB. These callers trigger index creation or regeneration when quality plans, characteristics, or specification value sets are defined or changed. Administrators generally encounter the package implicitly during plan setup, collection plan maintenance, or performance tuning of Quality queries involving external value tables. Because it issues DDL through AD_DDL, index operations are subject to the standard concurrent request processing and cannot be used inside a transaction expecting simple rollback semantics.