Results for “run_task”

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

Overview

SYS.DBMS_PARALLEL_EXECUTE is an Oracle-supplied PL/SQL package that provides a framework for executing a large DML operation in parallel by dividing the target data set into smaller, independently processable units called chunks. Although owned by SYS and declared AUTHID CURRENT_USER, it is exposed to the Oracle E-Business Suite 12.1.1 and 12.2.2 runtime through the standard database package layer and is available to any EBS schema that has been granted EXECUTE on it. In the EBS context, the package is chiefly used by technical consultants and developers who must apply bulk updates, deletes, or custom business logic across millions of rows without holding a single long-running transaction. Each chunk is processed by an independent worker session, which significantly reduces elapsed runtime and avoids the UNDO, redo, and rollback-segment pressure associated with monolithic operations. The package is documented in the ETRM as an "OTHER" classification API and is referenced by one other package in the source tree.

Key Procedures and Functions

The package groups its documented subprograms into task lifecycle, chunk creation, chunk retrieval, and execution control. All share the concept of a task_name, the user-visible identifier that links a task to its generated chunks and status rows; GENERATE_TASK_NAME produces a unique name from an optional prefix and CREATE_TASK registers the task with an optional comment, while DROP_TASK removes it. Chunk creation is performed by CREATE_CHUNKS_BY_ROWID (suitable for tables without a reliable numeric key), CREATE_CHUNKS_BY_NUMBER_COL (for tables with a sequential numeric column), and CREATE_CHUNKS_BY_SQL (for an arbitrary driving query). DROP_CHUNKS and PURGE_PROCESSED_CHUNKS manage chunk cleanup. Runtime retrieval is handled by GET_ROWID_CHUNK and GET_NUMBER_COL_CHUNK, which return the next unassigned chunk to a worker and mark it assigned; SET_CHUNK_STATUS records success or error at the chunk level. Execution is driven by RUN_TASK, with RESUME_TASK, STOP_TASK, and the internal RUN_INTERNAL_WORKER providing restart, halt, and worker behaviors. TASK_STATUS reports overall progress, and the administrative variants ADM_DROP_TASK, ADM_DROP_CHUNKS, ADM_TASK_STATUS, and ADM_STOP_TASK allow privileged operations on tasks owned by other sessions.

Tables Accessed

The package maintains its own dictionary of tasks and chunks within the invoking schema. Chunk state is tracked using the constants UNASSIGNED, ASSIGNED, PROCESSED, and PROCESSED_WITH_ERROR, and task state using CREATED, CHUNKING, CHUNKING_FAILED, NO_CHUNKS, CHUNKED, PROCESSING, FINISHED, FINISHED_WITH_ERROR, and CRASHED. These status values are persisted against the task_name and the identifying key of each chunk so that progress survives session restarts. The package does not read or write core EBS application tables directly; it is the developer's driving query or DML statement that touches the application data, executed once per chunk.

Usage Notes

DBMS_PARALLEL_EXECUTE is rarely invoked from standard EBS forms but is commonly embedded in custom concurrent programs, host scripts, or ad-hoc SQL*Plus sessions run by technical consultants. The typical sequence is to create a task, chunk the data, run the task with a user-supplied SQL or PL/SQL block, then query task status and purge chunks. The package raises predefined exceptions such as DUPLICATE_TASK_NAME, TASK_NOT_FOUND, CHUNK_NOT_FOUND, INVALID_STATUS, INVALID_TABLE, and the various INVALID_STATE_FOR_* conditions, all mapped to errors in the -29490 to -29499 range, so callers should handle them explicitly. In 12.1.1 and 12.2.2 the package is fully compatible with the EBS database tier provided adequate parallel execution and job queue resources are configured.