Results for “search_sql_string”

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

Overview

AMS_DiscovererSQL_PVT is a private (PVT) PL/SQL package owned by the APPS schema that supports the integration between Oracle E-Business Suite and Oracle Discoverer. Its role is to capture, persist, and interrogate the SQL statements that compose Discoverer workbook definitions. The package is declared with AUTHID CURRENT_USER, meaning its unqualified database references resolve against the privileges of the invoking user rather than the owning schema, which is consistent with a low-level utility that is called from within the Discoverer administration and validation flow. Through its two documented entry points, the package allows Discoverer workbooks to persist their generated SQL segments to the AMS_DISCOVERER_SQL table and allows those stored segments to be searched later, a capability used primarily by validation routines that need to confirm a workbook references expected tables, columns, or source objects. The package header carries a revision comment dating to 2002 and credits its creation to the internal developer tdonohoe, confirming its origin in the original Oracle Marketing/Discoverer integration delivered in early 11i releases and carried forward into 12.1.1 and 12.2.2.

Key Procedures and Functions

• EUL_TRIGGER$POST_SAVE_DOCUMENT — A function registered with Oracle Discoverer's Administration Edition as the callback invoked after a workbook document is saved. It accepts the workbook owner, workbook name, worksheet name, a sequence number, and a SQL segment, and returns a numeric status. Its purpose is to write each SQL segment that composes a workbook's query into the AMS_DISCOVERER_SQL table so the SQL can be searched subsequently. The package header explicitly notes that the function must be registered as a valid function in Discoverer Administration Edition before any workbook SQL will be saved; without that registration, no segments are persisted.
• SEARCH_SQL_STRING — A utility that searches a supplied search string across the set of stored SQL segments composing a workbook's SQL statement. It returns whether the string was found, indicates which SQL segment contained the match, reports the character position of the first character of the match within that segment, and, where a match spans two adjacent 2000-character segments, reports the overflow offset so callers can reassemble matches that cross segment boundaries. A parameter restricts the maximum number of characters searched. The header identifies "Validate SQL" as the primary caller.

Tables Accessed

The package reads and writes AMS_DISCOVERER_SQL, the table that stores the component SQL strings for saved Discoverer workbooks; this is the target of the post-save callback and the source for searches. AMS_DISCOVERER_SQL_S is the corresponding sequence (or shadow) object used to generate keys for those rows. AMS_LIST_SRC_TYPES is referenced in support of list source type resolution, consistent with the marketing source-type model that Discoverer-based list generation depends on. DUAL is used for singleton expression evaluation. All references are made through APPS synonyms, so the package operates entirely within the APPS namespace and does not address the underlying application schema objects directly.

Usage Notes

AMS_DiscovererSQL_PVT is a private package and is not intended for direct invocation by end users or by external custom code. It is invoked indirectly: EUL_TRIGGER$POST_SAVE_DOCUMENT fires automatically through the Discoverer Administration Edition post-save hook once it has been registered, and SEARCH_SQL_STRING is called by the "Validate SQL" routine that verifies workbook SQL. In 12.1.1 and 12.2.2 deployments the package remains part of the marketing/Discoverer integration and is depended upon by two other packages in the AMS schema. Because it is classified PVT, Oracle does not guarantee its signature across patches; customizations should not call it directly, and any validation or inspection of stored Discoverer SQL should be performed through supported validation flows rather than by invoking these routines independently.