Hello,
I'm creating a trigger to send an email if a procedure fails. The EXCEPTION in the procedure will insert records into a table:
CREATE TABLE ERR_TABLE
(
ERROR_NUMBER VARCHAR2(30 BYTE),
ERROR_MESSAGE VARCHAR2(200 BYTE),
ERROR_DATE DATE
)
The code for the trigger is the following:
CREATE OR REPLACE TRIGGER pw_expiry_error
BEFORE INSERT
ON err_table
FOR EACH ROW
DECLARE
err_msg err_table.error_message%TYPE;
BEGIN
FOR x IN -- select latest row created by error_date
--
(SELECT *
FROM (SELECT a.*, MAX (error_date) OVER () AS max_created
FROM err_table a)
WHERE error_date = max_created)
LOOP
SELECT error_message INTO err_msg FROM err_table;
send_mail ('email_from@domain.name',
'email_to@domain.name',
'Subject',
err_msg, -- body of message
'smtp.server',
'12');
END LOOP;
END;
/
In the LOOP, I'm receiving the following error when selecting into 'err_msg':
ORA-01422: exact fetch returns more than requested number of rows
The trigger should only pull the latest record entered into the ERR_TABLE. I'm not sure why it would return more than 1 record into the second SELECT statement?
Thanks in advance for your help!
Frank