How to capture the query throwing ORA-01427: single-row subquery returns more than one row

Published: 11 November 2022
on channel: Ken Kim

3,157

Application screen fails to load and logs show ORA--01427 error when calling a stored procedure with dynamic SQL. In this video I show the steps to easily identify the running SQL using the "after servererror on database" trigger.

CREATE TABLE SYS.DBA_SERVERERROR
(
ID NUMBER GENERATED ALWAYS AS IDENTITY ( START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER NOKEEP NOSCALE) NOT NULL,
ERROR_DATE DATE NOT NULL,
ORA_SYSEVENT VARCHAR2(128 BYTE),
ORA_LOGIN_USER VARCHAR2(128 BYTE),
ORA_SERVER_ERROR VARCHAR2(4000 BYTE),
SID NUMBER,
HOST VARCHAR2(256 BYTE),
IP VARCHAR2(15 BYTE),
MODULE VARCHAR2(4000 BYTE),
SERVERHOST VARCHAR2(256 BYTE),
SQL CLOB,
SQLID VARCHAR2(20)
);

CREATE OR REPLACE TRIGGER SYS.TRIG_AUDIT_ORA_01427
after servererror on database
declare
v_sql_text ora_name_list_t;
v_sql clob;
v_n number;
SQL_CODE VARCHAR2(100);
SQL_ERRM VARCHAR2(200);
SQLID VARCHAR2(20);
begin
--If you find a huge number of irrelevant errors, you might want to filter them out here.
--ORA-01427 single-row subquery returns more than one row
if ( is_servererror(1427) ) then
v_n := ora_sql_txt(v_sql_text);
for i in 1 .. v_n loop
v_sql := v_sql || v_sql_text(i);
end loop;

----https://carlos-sierra.net/2013/09/12/...
--SELECT fn_compute_sql_id(v_sql) INTO SQLID FROM DUAL;

insert into DBA_servererror(error_date, ora_sysevent, ora_login_user, ora_server_error, sid, host, ip, module, serverhost, sql)
values
(
sysdate,
ora_sysevent,
ora_login_user,
ora_server_error(1),
sys_context ('USERENV','SID'),
sys_context ('USERENV','HOST'),
sys_context ('USERENV','IP_ADDRESS'),
sys_context ('USERENV','MODULE'),
sys_context ('USERENV','SERVER_HOST'),
v_sql
);
commit;
end if;

--Never raise an exception from this trigger.
--No matter what happens we don't want recursive errors.
end;
/

Watch video How to capture the query throwing ORA-01427: single-row subquery returns more than one row online without registration, duration hours minute second in high quality. This video was added by user Ken Kim 11 November 2022, don't forget to share it with your friends and acquaintances, it has been viewed on our site 3,157 once and liked it 5 people.