Because UDFs are user-written applications, it is the responsibility of the user-written code to generate all success, warning, and exception messages.
The ANSI SQL:2011 standard defines a return code variable named SQLSTATE to accept status code messages. All condition messages are returned to this variable in a standard ASCII character string format.
All SQLSTATE messages are 5 characters in length. The first 2 characters define the message class and the last 3 characters define the message subclass.
For example, consider the SQLSTATE return code ‘22021’. The class of this message, 22, indicates a data exception condition. Its subclass, 021, indicates that a character not in the defined repertoire was encountered.
Be aware that SQL warnings do not abort a request, while SQL exceptions do abort a request.
Run UDFs in protected mode. Otherwise, the database might not trap errors that the function generates and a system error may occur. For more information, see When to Specify Unprotected Mode.
You should ensure that your UDFs always return valid SQLSTATE codes. The database does not map SQLSTATE values returned by user-defined functions to their equivalent SQLCODE values. All SQLSTATE codes generated by UDFs are explicitly mapped to database messages as indicated by the following table:
| UDF SQL Return Code | Error Message | Description |
|---|---|---|
| Warning | 7505 | *** Warning 7505 in dbname.udfname: SQLSTATE 01Hxx: < user function message text > |
| Exception | 7504 | *** Error 7504 in dbname.udfname: SQLSTATE U0xxx: < user function message text > |
The string represented by <user function message text> is a user-defined error message generated by the UDF.
The text represented by x characters in the SQLSTATE subclass is also assigned by the user-written UDF code. All characters must be either digits or uppercase Latin characters.
For more information about coding UDFs, see Teradata Vantage™ - SQL External Routine Programming, B035-1147.
For more information about SQL exception and warning codes, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
For more information about SQLSTATE and SQLCODE, see Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148.