Search Results rollback_proc




Overview
Explore in AI Chat

Overview

XDP_PROCEDURE_UTIL is a utility package owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the XDP module family, which supports the generation and management of dynamically constructed PL/SQL program units. The package is declared with AUTHID CURRENT_USER, meaning that all SQL statements it executes are parsed and run under the privileges of the invoking user rather than the package owner, which is significant because the package creates and manipulates database objects such as package specifications and bodies.

Functionally, XDP_PROCEDURE_UTIL acts as a programmatic DDL and metadata controller. It provides the runtime infrastructure required to register, build, and roll back generated procedures and to resolve procedure names to their owning package names. Rather than serving a single business transaction, the package exists as plumbing that allows higher-level XDP components to persist procedure source text into repository tables and then materialize those definitions as real database objects. Because it participates in object creation and cleanup, it is central to any feature that relies on dynamic procedure generation.

Key Procedures and Functions

  • GET_PACKAGE_SPEC — A function that returns the package specification text corresponding to a supplied procedure type.
  • CREATE_PACKAGE_SPEC — Accepts a procedure name and procedure type, and creates the associated package specification. It returns a numeric return code and an error string through OUT parameters.
  • CREATE_PACKAGE_BODY — Creates the package body for the supplied procedure, taking additional function or feature identifiers and the procedure body text. It likewise reports status via return code and error string.
  • LOAD_PROC_TABLE — Loads procedure metadata and source text into the supporting repository table, establishing the record used by later build or rollback operations.
  • ROLLBACK_PROC — Removes or reverts a previously created procedure using the procedure name, procedure type, and associated function and feature identifiers. It returns a return code and error string.
  • GET_PACKAGE_NAME — Resolves a procedure name to its containing package name, returning the result together with a return code and error string.
  • DECODE_PROC_NAME — A function that decodes or normalizes a supplied procedure name and returns the decoded value as a string.

The common signature pattern in the documented procedures is a set of IN parameters followed by return_code OUT NOCOPY NUMBER and error_string OUT NOCOPY VARCHAR2. This consistent convention is why the search term "error_string" surfaces this package: callers inspect the error string to distinguish successful completions from failures during dynamic DDL or metadata loading.

Tables Accessed

  • XDP_PROC_BODY — Referenced through an APPS synonym. This table stores the procedure body source and related metadata. LOAD_PROC_TABLE writes records here, and CREATE_PACKAGE_BODY and GET_PACKAGE_SPEC read from it to reconstruct or generate package definitions.
  • USER_ERRORS — The standard Oracle data dictionary view listing compilation errors for stored objects owned by the current user. Because the package compiles generated package specifications and bodies, it consults USER_ERRORS to detect and report compilation failures. The AUTHID CURRENT_USER declaration ensures errors are resolved against the invoker's schema.

Usage Notes

XDP_PROCEDURE_UTIL is not normally invoked directly by end users. It is called by higher-level XDP packages and by custom code that generates procedures at runtime. In Oracle EBS 12.1.1 and 12.2.2 deployments, it supports features that require on-the-fly construction of PL/SQL program units, such as dynamic rule or procedure registration. Callers are expected to check the return_code and error_string OUT parameters after every call and to treat any nonzero return code as a failure. Because the package performs DDL under invoker's rights, the calling user must hold the necessary privileges to create and drop the target objects. The package is documented as referenced by one other package, confirming its role as a dependent utility rather than a top-level entry point.