Press "Enter" to skip to content

DBMS_ERROR_TEXT built-in Oracle D2k

DBMS_ERROR_TEXT built-in Oracle D2k

Description
Returns the message number (such as ORA-01438) and message text of the database error.
Syntax
FUNCTION DBMS_ERROR_TEXT;
Built-in Type unrestricted function
Enter Query Mode yes
Parameters
none
Usage Notes
You can use this function to test database error messages during exception handling routines.
DBMS_ERROR_TEXT returns the entire sequence of recursive errors.
DBMS_ERROR_TEXT examples
/*
** Built-in: DBMS_ERROR_CODE,DBMS_ERROR_TEXT
** Example: Reword certain Form Builder error messages by
** evaluating the DBMS error code that caused them
** trigger: On-Error
*/
DECLARE
errcode NUMBER := ERROR_CODE;
dbmserrcode NUMBER;
dbmserrtext VARCHAR2(200);
BEGIN
IF errcode = 40508 THEN
/*
** Form Builder had a problem INSERTing, so
** look at the Database error which
** caused the problem.
*/
dbmserrcode := DBMS_ERROR_CODE;
dbmserrtext := DBMS_ERROR_TEXT;
IF dbmserrcode = -1438 THEN
/*
** ORA-01438 is “value too large for column”
*/
Message(’Your number is too large. Try again.’);
ELSIF dbmserrcode = -1400 THEN
/*
** ORA-01400 is “Mandatory column is NULL”
*/
Message(’You forgot to provide a value. Try again.’);
ELSE
/*
** Printout a generic message with the database
** error string in it.
*/
Message(’Insert failed because of ’||dbmserrtext);
END IF;
END IF;
END;