CREATE_EXCEPTION_VIEW procedure

Creates a view based on the specified exception table which interprets the content of the EXCEPT_MSG column.

Prototypes

CREATE_EXCEPTION_VIEW(SOURCE_SCHEMA VARCHAR(128), SOURCE_TABLE VARCHAR(128), DEST_SCHEMA VARCHAR(128), DEST_VIEW VARCHAR(128))
CREATE_EXCEPTION_VIEW(SOURCE_TABLE VARCHAR(128), DEST_VIEW VARCHAR(128))
CREATE_EXCEPTION_VIEW(SOURCE_TABLE VARCHAR(128))

Description

The CREATE_EXCEPTION_VIEW procedure creates a view on top of an exceptions table (presumably created with CREATE_EXCEPTION_TABLE procedure). The view uses a recursive common-table-expression to split the large EXCEPT_MSG field into several rows and several columns to allow for easier analysis. Instead of EXCEPT_MSG, the view contains the following exceptions-related fields:

EXCEPT_TYPE

A CHAR(1) column containing one of the following values:

  • 'K' - check constraint violation
  • 'F' - foreign key violation
  • 'G' - generated column violation
  • 'I' - unique index violation
  • 'L' - datalink load violation
  • 'D' - cascaded deletion violation
EXCEPT_OBJECT
A VARCHAR(n) column containing the fully qualified name of the object that caused the exception (e.g. the name of the check constraint, foreign key, column or unique index)

Like CREATE_EXCEPTION_TABLE procedure, this procedure has only one mandatory parameter: SOURCE_TABLE. If SOURCE_SCHEMA and DEST_SCHEMA are not specified, they default to the value of the CURRENT SCHEMA special register. If DEST_VIEW is not specified, it defaults to the value of SOURCE_TABLE with a '_V' suffix.

Note

SELECT and CONTROL authorizations are copied from the source table to the destination view (INSERT, UPDATE, and DELETE authorizations are ignored).

Parameters

SOURCE_SCHEMA
If provided, the schema containing the exception table on which to base the new view. Defaults to the value of the CURRENT SCHEMA special register if omitted.
SOURCE_TABLE
Specifies the exception table on which to base the new view. This table is expected to have two columns named EXCEPT_TS and EXCEPT_MSG.
DEST_SCHEMA
If provided, the schema in which to create the new view. Defaults to the value of the CURRENT SCHEMA special register if omitted.
DEST_VIEW
If provided, the name of the new view. Defaults to SOURCE_TABLE with a '_V' suffix if omitted.

Examples

Create a view to interpret the content of EXCEPTIONS.LEDGER called FINANCE.LEDGER_EXCEPTIONS:

CALL CREATE_EXCEPTION_VIEW('EXCEPTIONS', 'LEDGER', 'FINANCE', 'LEDGER_EXCEPTIONS');

Create a view called EMPLOYEE_EXCEPTIONS_V based on the EMPLOYEE_EXCEPTIONS table in the current schema:

CALL CREATE_EXCEPTION_VIEW('EMPLOYEE_EXCEPTIONS');