Created
March 14, 2018 18:08
-
-
Save frank-borkin/a4549c199cdb00d4a1f6e413f5edc29d to your computer and use it in GitHub Desktop.
Catch bad queries in Oracle.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table caught_errors ( | |
dt date, | |
username varchar2( 30), -- value from ora_login_user | |
msg varchar2(2000), | |
stmt varchar2(2000) | |
); | |
create or replace trigger catch_errors | |
after servererror on database | |
declare | |
sql_text ora_name_list_t; | |
msg_ varchar2(2000) := null; | |
stmt_ varchar2(2000) := null; | |
begin | |
for depth in 1 .. ora_server_error_depth loop | |
msg_ := msg_ || ora_server_error_msg(depth); | |
end loop; | |
for i in 1 .. ora_sql_txt(sql_text) loop | |
stmt_ := stmt_ || sql_text(i); | |
end loop; | |
insert into | |
caught_errors (dt , username ,msg ,stmt ) | |
values (sysdate, ora_login_user,msg_,stmt_); | |
end; | |
/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this trigger, then just select * from caught_errors;
Simple!
WARNING - dropping this table without removing the trigger will cause an error, which will try to write to the table, which will cause an error and so on. You have been warned.