Overview
- As part of any application development, error / exception handling is a key process which make the application more interactive and provide a user friendly message to the users to take necessary corrective actions. Most of the time the functional error messages are all hard coded while handling an error, however, this will not be in an organized way and will be challenging to make any changes to the error message or to bring uniformity in the process.
- This document explain the better way of configuring our functional error / validation messages and maintaining in a single place with more organized manner and provides the reusability feature.
Technologies and Tools Used
The following technologies has been used to achieve this in oracle apex.
- Oracle Apex.
Use Case
- Avoiding hard code in error message.
Steps
Steps to be followed
Step1: Create a table for error message and error code.
Step2: Create a PLSQL function to get error message.
Example
VALIDATION
Here, I have created a validation for “Department Name is mandatory” and “Invalid Department Name”. GET_ERR_MSG_FN function is used to get message instead of writing hard code. We can use this function wherever we need to check Department Name is mandatory and Invalid Department Name.
Validation Code.
BEGIN
IF :lv_v_dept_name IS NULL THEN
RAISE_APPLICATION_ERROR (‘-20001’, ERROR_MESSAGE_PKG .GET_ERR_MSG_FN (‘ DEPARTMENT- MANDATORY’,lv_v_dept_name));
ELSIF :lv_v_dept_name <> ‘Human Resources’ THEN
RAISE_APPLICATION_ERROR (‘-20002’, ERROR_MESSAGE_PKG.GET_ERR_MSG_FN (‘ INVALID- DEPARTMENT NAME’, :lv_v_dept_name));
END IF;
END;
OUTPUT:
- If department name is null, Error message is “Department Name is mandatory’”.
- If department name is not equal to Human Resources, Error message is “Invalid Department Name’ ”
For example: If I give the lv_dept_name as “Finance”, I receive an error message as “Invalid Department Name Finance”. (Here, we can get the error message with the incorrectly given parameter.)
Step1: Create a table for error message and error code.
Here I have created an ERROR_CODE_TB table. You can add any columns you need. I have just added three columns.
CREATE TABLE ERROR_CODE_TB
(
ERROR_CODE VARCHAR2 (30),
ERROR_MSG VARCHAR2 (100),
PARAMSPATTERN VARCHAR2 (2)
);
NOTE: The PARAMSPATTERN column is used to obtain the incorrectly given parameter
I have inserted two records
INSERT INTO ERROR_CODE_TB VALUES (‘DEPARTMENT-MANDATORY’, ‘Department Name is mandatory’’, NULL);
INSERT INTO ERROR_CODE_TB VALUES (‘INVALID-DEPARTMENT NAME’, ‘Invalid Department Name @’,’@’);
Step2: Create a PLSQL function is used to get error message from the table.
FUNCTION – GET_ERR_MSG_FN
There are three function inside this function. They are
- FETCH_ERR_MSG_FN – It is used to get an error code.
- FETCH_ERR_PAT_FN – It is used to get pattern.
- BUILD_ERR_MSG_FN – It is used to get an error message with an incorrectly entered parameter.
Package Specification Part:
CREATE OR REPLACE PACKAGE ERROR_MESSAGES_PKG AS
FUNCTION GET_ERR_MSG_FN (P_ERR_CODE VARCHAR2, P_PARAMETER VARCHAR2, P_SEPERATOR VARCHAR2 DEFAULT ‘,’)
RETURN VARCHAR2;
FUNCTION FETCH_ERR_MSG_FN (P_ERR_CODE VARCHAR2) RETURN VARCHAR2 RESULT_CACHE;
FUNCTION FETCH_ERR_PAT_FN (P_ERR_CODE VARCHAR2)
RETURN VARCHAR2
FUNCTION BUILD_ERR_MSG_FN (P_MESSAGE IN VARCHAR2,
P_PARAMS IN VARCHAR2,
P_PARAMSPATTERN IN VARCHAR2,
P_PARAMSEPERATOR IN VARCHAR2)
RETURN VARCHAR2;
END ERROR_MESSAGES_PKG;
Package Body Part:
CREATE PACKAGE BODY ERROR_MESSAGE_PKG AS
FUNCTION GET_ERR_MSG_FN (P_ERR_CODE VARCHAR2,
P_PARAMETER VARCHAR2,
P_SEPERATOR VARCHAR2 DEFAULT ‘,’)
RETURN VARCHAR2
AS
L_SEPERATOR VARCHAR2 (25) := P_SEPERATOR;
L_PARAMETER VARCHAR2 (500) := P_PARAMETER;
LERRMSG VARCHAR2 (4000);
L_PARAMSPATTERN ERR_CODE_TB.PARAMSPATTERN%TYPE;
BEGIN
LERRMSG := FETCH_ERR_MSG_FN(P_ERR_CODE);
L_PARAMSPATTERN := FETCH_ERR_PAT_FN(P_ERR_CODE);
LERRMSG := BUILD_ERR_MSG_FN(LERRMSG,L_PARAMETER,L_PARAMSPATTERN,L_SEPERATOR);
RETURN LERRMSG;
EXCEPTION WHEN OTHERS THEN
LERRMSG := SQLERRM;
ERROR_PKG.ERROR_LOG (LERRMSG,’EXCEPTION’);
END;
—-1. FETCH_ERR_MSG_FN – It is used to get an error code.
FUNCTION FETCH_ERR_MSG_FN (P_ERR_CODE VARCHAR2)
RETURN VARCHAR2
RESULT_CACHE
AS
LERRMSG VARCHAR2(4000);
BEGIN
SELECT ERROR_MSG into LERRMSG from ERR_CODE_TB where error_code = P_ERR_CODE;
RETURN LERRMSG;
EXCEPTION WHEN OTHERS THEN
LERRMSG := SQLERRM;
ERROR_PKG.ERROR_LOG (LERRMSG,’EXCEPTION’);
RETURN NULL;
END FETCH_ERR_MSG_FN;
—-2. FETCH_ERR_PAT_FN – It is used to get pattern.
FUNCTION FETCH_ERR_PAT_FN (P_ERR_CODE VARCHAR2)
RETURN VARCHAR2
RESULT_CACHE
AS
L_PARAMSPATTERN ERR_CODE_TB.PARAMSPATTERN%TYPE;
LERRMSG VARCHAR2 (4000);
BEGIN
SELECT PARAMSPATTERN into L_PARAMSPATTERN from ERR_CODE_TB WHERE error_code = P_ERR_CODE;
RETURN L_PARAMSPATTERN;
EXCEPTION WHEN OTHERS THEN
LERRMSG := SQLERRM;
ERROR_PKG.ERROR_LOG (LERRMSG,’EXCEPTION’);
END FETCH_ERR_PAT_FN;
—-3. BUILD_ERR_MSG_FN – It is used to get error message with parameter which is wrongly given
FUNCTION BUILD_ERR_MSG_FN (
p_message IN VARCHAR2,
p_params IN VARCHAR2,
p_paramspattern IN VARCHAR2,
p_paramseperator IN VARCHAR2
)
RETURN VARCHAR2
AS
l_cnt NUMBER := 0;
l_spos NUMBER;
l_message VARCHAR2 (4000) := p_message;
lerrmsg VARCHAR2 (4000);
lblock VARCHAR2 (50) := ‘BUILD_ERR_MSG_FN’;
BEGIN
l_cnt := regexp_count (p_params, p_paramseperator);
l_spos := 1;
IF l_cnt <> 0
THEN
FOR i IN 1 .. l_cnt
LOOP
l_message :=
REGEXP_REPLACE (l_message,
p_paramspattern,
SUBSTR (p_params,
l_spos,
INSTR (p_params, p_paramseperator, 1, i)
– l_spos
),
1,
1
);
l_spos := INSTR (p_params, p_paramseperator, 1, i) + 1;
END LOOP;
END IF;
RETURN l_message;
EXCEPTION
WHEN OTHERS
THEN
lerrmsg := SQLERRM;
ERROR_PKG.ERROR_LOG (lerrmsg, ‘EXCEPTION’);
END BUILD_ERR_MSG_FN;
END ERROR_MESSAGE_PKG;
