17.10 - Error Handling - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - XML Data Type

Advanced SQL Engine
Teradata Database
Release Number
July 2021
Last Update
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

In batch mode shredding, by default, if an error occurs while an individual document is being processed, the batch is aborted and the stored procedure returns an error message indicating the type of error that occurred.

In some cases, the user may prefer to continue processing the remaining documents in the batch, and write the errors to a specified error table for later triage and resolution. This permissive mode of shredding can be invoked by passing in the errorTable option as part of the externalContext parameter to the batch shredding stored procedure.

The externalContext parameter is a comma separated list of name=value pairs. To invoke permissive error handling, users can specify errorTable as one of the names in the externalContext, with a table name as its value. The table name can be qualified with its database name. If not qualified, the table should reside under the user invoking the shredding stored procedure. While shredding a batch of documents, if any individual document fails to be shredded, its document ID and an error message will be written to the error table.

The error table should be a multiset table, and it should have the following two columns:
Additional columns can be defined, but these columns should either be nullable or have a default value defined. Teradata recommends adding a column that will help with identifying the time when the error occurred. For example:
An error is raised if any of the following is true:
  • The error table does not exist.
  • The error table does not have the docid and errmsg columns.
  • The user invoking the shredding procedure does not have insert privileges on the error table.

XML batch shredding is done in two phases. In the first phase, all the documents in the batch are shredded to a volatile table, and in the second phase, data is transferred from the volatile table to the target tables. During the first phase, if any document fails to shred for any reason, the document ID and the error message corresponding to the failure are written to the error table. This allows the stored procedure to continue shredding the remaining documents in the batch. If, however, an error occurs in the second phase (while moving data from the volatile table to the target tables), the shredding procedure will abort without writing to any of the target tables.