Is it possible in Oracle to trace SQL statements that result in errors?

Question

We have Oracle 11g in production. Application system is still under active development. It will be very handy to get SQL statements which cause any error.

Does Oracle provide a standard function to trace and log these statements and additional (debug) info?

Answer 1

While I'm not sure if it's the easiest way, you can use an after servererror on database trigger to log all errors to a table.

From Log all database errors to a table by Ferdous Nadvi:

drop trigger log_errors_trig;
drop table   log_errors_tab;

create table log_errors_tab (
 error     varchar2(30),
 timestamp date,
 username  varchar2(30),
        osuser    varchar2(30),
        machine   varchar2(64),
 process   varchar2(8),
 program   varchar2(48));

create or replace trigger log_errors_trig 
 after servererror on database
declare
 var_user     varchar2(30);
 var_osuser   varchar2(30);
 var_machine  varchar2(64);
 var_process  varchar2(8);
 var_program  varchar2(48);
begin
 select username, osuser, machine, process, program
 into   var_user, var_osuser, var_machine, var_process, var_program
 from   sys.v_$session
 where  audsid = userenv('sessionid');

 insert into log_errors_tab
   values(dbms_standard.server_error(1),sysdate,var_user,
          var_osuser,var_machine,var_process,var_program);
end;
/

See also Oracle System Event Triggers on PSOUG and the official documentation for system event triggers

Answer 2

To find failed queries. Use following plsql trigger to find queries.

CREATE TABLE QUERY_LOG (ID              NUMBER,ORA_ERR         VARCHAR2(10),EXE_DATE        DATE,QRY_LOG         NCLOB);

CREATE SEQUENCE QUERY_LOG_ID NOCACHE;

CREATE OR REPLACE TRIGGER Failed_To_Extend_Temp
AFTER SERVERERROR ON DATABASE
DECLARE
    l_sql_text ora_name_list_t;
    l_n        NUMBER;
    t1         NCLOB;
BEGIN
    --IF (is_servererror(942) ) THEN
    l_n := ora_sql_txt(l_sql_text );

    FOR I IN 1 .. l_n
    LOOP
        t1 := t1||l_sql_text(i);
    END LOOP;

    INSERT INTO QUERY_LOG (Id , Ora_Err , EXE_DATE , QRY_LOG)
    VALUES (QUERY_LOG_ID.Nextval, ora_server_error(1), Sysdate,t1 );
--END IF;
END;
/

See my YouTube video Oracle, Find Failed Queries