pvsbandi
asked on
Postgres Custom Error messages
Hi,
I have the below Stored Procedure in Postgres.
I have an error log table. When someone passes the V_START_DT to be greater than V_END_DT, i want to insert a custom SQLState and SQLERRM into the error log table.
What i've coded, isn't working. Can someone kindly point me to the solution?
I have the below Stored Procedure in Postgres.
I have an error log table. When someone passes the V_START_DT to be greater than V_END_DT, i want to insert a custom SQLState and SQLERRM into the error log table.
What i've coded, isn't working. Can someone kindly point me to the solution?
CREATE OR REPLACE FUNCTION F_ERR_VALIDATION(V_ID INT
,V_NAME VARCHAR(50)
,V_START_DT DATE
,V_END_DT DATE
)
RETURNS VOID
AS
$$
DECLARE LV_ERROR_CODE VARCHAR(20);
LV_ERROR_MSG VARCHAR(200);
BEGIN
INSERT INTO TB_CLIENT
VALUES (V_ID,V_NAME,V_START_DT,V_END_DT);
EXCEPTION WHEN (V_START_DT > V_END_DT) THEN
SQLSTATE := '-1';
SQLERRM := 'Start Date Cannot be greater than End date';
INSERT INTO TB_ERROR_LOG
VALUES ('TB_CLIENT',V_ID,SQLSTATE,SQLERRM);
END IF;
EXCEPTION WHEN OTHERS THEN
LV_ERROR_CODE = SQLSTATE;
LV_ERROR_MSG = SQLERRM;
INSERT INTO TB_ERROR_LOG
VALUES ('TB_CLIENT',V_ID,LV_ERROR_CODE,LV_ERROR_MSG);
END $$ LANGUAGE PLPGSQL;
ASKER
Thanks!
When I compile, it throws an error
SQL State 42704
Unrecognized exception condition "v_start_dt"
Looks like EXCEPTION WHEN v_start_dt is not correct
When I compile, it throws an error
SQL State 42704
Unrecognized exception condition "v_start_dt"
Looks like EXCEPTION WHEN v_start_dt is not correct
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.
Do not try set SQLSTATE AND SQLErrm and directly insert the values into our log table.
CREATE OR REPLACE FUNCTION F_ERR_VALIDATION(V_ID INT
,V_NAME VARCHAR(50)
,V_START_DT DATE
,V_END_DT DATE
)
RETURNS VOID
AS
$$
DECLARE LV_ERROR_CODE VARCHAR(20);
LV_ERROR_MSG VARCHAR(200);
BEGIN
INSERT INTO TB_CLIENT
VALUES (V_ID,V_NAME,V_START_DT,V_
EXCEPTION WHEN (V_START_DT > V_END_DT) THEN
INSERT INTO TB_ERROR_LOG
VALUES ('TB_CLIENT',V_ID,'-1','St
END IF;
EXCEPTION WHEN OTHERS THEN
LV_ERROR_CODE = SQLSTATE;
LV_ERROR_MSG = SQLERRM;
INSERT INTO TB_ERROR_LOG
VALUES ('TB_CLIENT',V_ID,LV_ERROR
END $$ LANGUAGE PLPGSQL;