I have gone thru some docs and books on dev to understand error messages in Developer. After reading i am more confused reg the difference between
Dbms_Error_Code, Error_Code and SqlCode.
Can any one tell me concisely the difference and the situation where these are used.
This is my understanding so far.....
DBMS_ERROR_CODE and DBMS_ERROR_TEXT return the last Oracle Server error code and message due to an implicit DML within form application.
SQLCODE and SQLERRM return the last Oracle Server error code and message due to an explicit DML within a form application.
So why use ERROR_CODE at all?
Below is an extract from the Oracle help Docs...
When an implicit DML raises an error, it is handled with an ON-ERROR trigger using the ERROR_CODE function. This function contains the last Oracle Form error code. These errors are prefixed with FRM-.
Okay but why not just use DBMS_ERROR_CODE instead of ERROR_CODE?
Example:
/*
** Built-in: DBMS_ERROR_CODE,DBMS_ERROR_TEXT
** Example: Reword certain Oracle Forms 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
/*
** Oracle Forms 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;
Regards
Gus