DBMS_UTILITY Package Design

The package definition and C implementation are under contrib/ivorysql_ora/src/builtin_packages/dbms_utility/. PL/iSQL files are under src/pl/plisql/src/. Regression SQL and expected output are in the extension’s sql/ and expected/ directories. The table lists filenames only.

The extension’s Makefile, meson.build, and ivorysql_ora_merge_sqls integrate the C module, SQL script, and regression tests into build and installation.

The three package functions take no arguments and return text. Their bodies call the corresponding sys.ora_format_* C functions. The C entry points are VOLATILE because results depend on the current execution and exception context.

The C module uses dlsym(RTLD_DEFAULT, …​) to find symbols exported by the loaded PL/iSQL module, avoiding a link-time dependency on plisql.so. The first lookup results are cached in backend-static function pointers for subsequent calls. The current implementation attempts lookup only once.

The four PL/iSQL APIs provide:

If an API cannot be obtained, the corresponding package function returns NULL. PL/iSQL owns the exception message and context. The call-stack API returns a newly allocated string that the consumer frees after use.

pl_exec.c uses exception_handling_estate to identify the execution state handling an exception. On entry to a matching handler, it saves the previous pointer and switches to the current state. It restores the previous value when the handler completes normally.

Although a diagnostic runs in another package function invocation, this pointer lets it read the handler’s cur_error rather than its own execution state. After a nested exception is handled and the subprogram returns normally, the outer context is restored, so repeated reads of the outer backtrace agree.

The function reads exception context and transforms it line by line. transform_and_append_line() recognizes PL/iSQL function and PL/pgSQL function lines, extracting names and line N. It skips SQL statement lines and other unrecognized formats.

Named routines produce ORA-06512: at "object name", line N; anonymous blocks produce ORA-06512: at line N. Lines retain the original exception context order, from the exception origin toward outer callers.

PL/iSQL constructs schema, package, and subprogram names for package members using package catalog information. Formatting detects already qualified names to avoid adding another schema. Unqualified standalone routines still fall back to public; this conversion function does not look up their actual owning schema.

The function reads the exception message and SQLSTATE, selects a number with sqlstate_to_ora_errnum(), and produces ORA-XXXXX: original message followed by a newline. It does not automatically append a location backtrace or translate the original message.

The mapping preserves common categories, but the generic number cannot distinguish every underlying exception. A missing message or SQLSTATE of 0 returns NULL.

plisql_get_call_stack() walks error_context_stack, collecting only frames whose callback is plisql_exec_error_callback. Each frame contains the execution object’s address, current line number, and signature, with qualified names for package members.

C formatting skips the diagnostic function’s own first frame, places the remaining frames after the fixed PL/SQL Call Stack header, and labels all of them function. No caller frames to display produces NULL. This path reads the current execution stack, separately from the exception path saved in cur_error→context.

Backtraces and call stacks pass names through apply_identifier_case_switch(). With case switching disabled, or mode normal, the text is unchanged. lowercase downcases all-uppercase text. interchange swaps all-uppercase and all-lowercase text, leaving mixed-case text unchanged.

This processing applies to the retrieved name text, so displayed results depend on session configuration. Call-stack handles are runtime addresses rather than persistent object identifiers.