Search Results get_children

Overview

CTXSYS.CTX_TREE is an Oracle Text (interMedia Text) PL/SQL package supplied by the CTXSYS schema and declared with AUTHID current_user in Oracle E-Business Suite 12.1.1 and 12.2.2. It is not an Applications-owned API; it belongs to the Oracle Text option bundled with the database, and EBS references it through the standard CTXSYS grants and synonyms exposed to the APPS schema. Functionally, CTX_TREE implements a hierarchical, path-based index over text stored in MVDATA sections of an Oracle Text index. Each registered index and MVDATA section pair is modelled as a tree, where individual paths represent membership relationships and each path is assigned a system-generated numeric node identifier. This allows applications to perform fast ancestor, descendant, and sibling lookups against large sets of hierarchical values without recursively querying a relational hierarchy table. Within an EBS context the package is an infrastructure component rather than a user-facing feature; no standard Oracle Forms, concurrent program, or EBS workflow in release 12.1.1 or 12.2.2 calls it directly in delivered code, and the ETRM records only a single referencing package. It is therefore most commonly encountered in custom Oracle Text implementations, in extended search configurations, and during support investigation of database-level dependencies rather than in day-to-day EBS transaction processing.

Key Procedures and Functions

• REGISTER_INDEX — Registers a named Oracle Text index with the CTX_TREE package, establishing the index as a target for subsequent tree operations on its MVDATA sections.
• ADD_NODE — Adds a new path to the tree associated with a given index and MVDATA section, returning the node identifiers created for that path. A force option permits completion when the path already exists.
• GET_NODE_ID — Resolves an existing path in a tree to its corresponding numeric node identifier.
• GET_CHILDREN — Returns the child node names of the node identified by a given path within a tree associated with an MVDATA section and index. This is the procedure most frequently searched for by developers working with hierarchical text search results.
• GET_NODEID_LIST — Returns the list of node identifiers associated with a path or node, supporting bulk resolution of hierarchy members.
• GET_ROOTS — Returns the root nodes of a tree, providing the entry point for top-down traversal.
• REMOVE_NODE — Removes a node or path from the tree, deleting it from the registered index structure.
• DROPSEQUENCES — Drops the internal sequences used by the package for node identifier generation, typically during cleanup or deinstallation.
• REGINDEX — An index registration routine functionally related to REGISTER_INDEX, used to place an index under CTX_TREE management.

Tables Accessed

The documented table reference for this package is PLITBLM, accessed through APPS synonyms. PLITBLM is the Oracle Text dr$ table used to record index-processing state information, particularly for pending or in-progress index operations. CTX_TREE reads and writes this table as part of coordinating node registration and index maintenance so that tree structures remain consistent with the underlying Oracle Text index. Additional CTX_TREE internal objects exist within the CTXSYS schema, but PLITBLM is the only table recorded in the ETRM metadata for this object.

Usage Notes

CTX_TREE is invoked directly in PL/SQL, not from EBS forms or concurrent programs. Typical use is in custom code that builds or queries a hierarchy over MVDATA content: register the index, add nodes for the desired paths, then resolve paths and enumerate children using GET_NODE_ID, GET_CHILDREN, GET_NODEID_LIST, and GET_ROOTS. Because the package executes with definer-invoker semantics under AUTHID current_user, the calling schema must hold EXECUTE privileges on CTXSYS.CTX_TREE and the necessary rights on the referenced index objects. In EBS 12.1.1 and 12.2.2 environments, such grants are normally channelled through the APPS schema using the standard CTXSYS synonym set. Changes to EBS code or Oracle Text indexes that touch this package should be treated as database-level customisations and validated against both release levels, since the package is not covered by the standard EBS patching cycle.