Autonomous procedures

Autonomous procedures execute under their own units of work, separate from the calling program, and commit when they finish without committing the work of the calling program.

Autonomous procedures execute as separate units of work that are independent from the calling application programs. Autonomous procedures follow the rules of the COMMIT ON RETURN YES option for their changes before returning to the caller. However, their commit does not impact changes completed by the calling application program. The calling application program controls when its own updates are committed or rolled back.

If the calling application rolls back its own changes, the committed changes of the autonomous procedure are not affected. Therefore, autonomous procedures are useful for logging information about error conditions encountered by an application program. When the application encounters the error and rolls back its own changes, the committed changes of the autonomous procedure remain available.

Autonomous procedures can be called by normal application programs, other stored procedures, user-defined functions or triggers. Autonomous procedures can complete the following types of work:

  • Execute SQL statements
  • Invoke another procedure, function, or trigger, as long as the number of nested levels does not exceed 64, and the called procedure is not autonomous.
  • Execute COMMIT and ROLLBACK statements that apply to the SQL operations executed by nested processes within the autonomous procedure.

The following restrictions apply to autonomous procedures:

  • Only native SQL procedures can be defined as autonomous.
  • Autonomous procedures and nested procedure, triggers, and functions within autonomous procedures cannot invoke other autonomous procedures.
  • Autonomous procedures cannot see uncommitted changes from the calling application.
  • When multiple versions of a procedure exist, all versions must be defined as autonomous.
  • Autonomous procedures do not share locks with the calling application, meaning that the autonomous procedure might timeouts because of lock contention with the calling application.
  • Parallelism is disabled for autonomous procedures. All statements in an autonomous procedure and for any nested levels within are run in sequential processing mode.
  • DYNAMIC RESULT SETS 0 must be specified when autonomous procedures are used.
  • Stored procedure parameters must not be defined as a LOB data type, or any distinct data type that is based on a LOB or XML value.