How to Log All Failed SQL Statements in Oracle 10G

Oracle - query failed statements

Blaim me if I'm wrong but as far as I understand sql gets logged on shared pool check and statements Laszlo mentioned select nonexistingcolumn from dual; failed on semantic check. My answer is you won't find invalid statements in DB.
https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#TGSQL178

How to log all exceptions in Oracle package?

You can't use SQLERRM directly - you have to assign it to an intermediate variable. Note that Oracle 9i would let you get away with it, but that has always been the documented behavior. See here for some sample code.

You could also consider wrapping this bit in an autonomous transaction, so it gets logged even if your PL/SQL code's transaction gets rolled back.

Oracle - Log the executed query

Unfortunately, this field restricted by 1Kb

If you need the full SQL, then use the SQL_FULLTEXT which is a CLOB datatype instead of SQL_TEXT whcih is limited to first 1000 characters.

From documentation,

Column          Datatype        Description
------          --------------  ---------------------------------------

SQL_TEXT        VARCHAR2(1000)  First thousand characters of the SQL 
                                text for the current cursor

SQL_FULLTEXT    CLOB            Full text for the SQL statement exposed 
                                as a CLOB column. The full text of a SQL
                                statement can be retrieved using this 
                                column instead of joining with the 
                                V$SQL_TEXT dynamic performance view.

So, use:

SELECT SQL_FULLTEXT FROM v$sql;

By the way, seems like you are actually looking for tracing your session to get the complete details of the procedure and the SQL statements involved. I would suggest to trace the session with level 4 i.e. with the addition of bind variable values.

See How to generate trace file – SQL Trace and TKPROF in Oracle

Oracle: Is there a way to get recent SQL syntax errors?

You can create a trigger in Oracle that will log all errors (or pretty much all - NO_DATA_FOUND is not considered an error). In the example below, any error in the schema is recorded in the TRACK_DETAIL table (error in one row, failed SQL in the next). You can make it more sophisticated with a sequence number, date/time etc.

create table track_detail (val varchar2(4000));

create or replace procedure track (p_text IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  insert into track_detail(val)
  values (p_text);
  commit;
end;
.
/
create or replace TRIGGER log_err after servererror on schema
DECLARE
  v_temp VARCHAR2(2000) := substr(dbms_utility.format_error_stack,1,2000);
  v_num NUMBER;
  v_sql_text ora_name_list_t;
begin
  v_temp := translate(v_temp,'''','"');
  track(v_temp);
  v_num  := ora_sql_txt(v_sql_text);
  v_temp := null;
  BEGIN
    FOR i IN 1..v_num LOOP
      v_temp := v_temp || v_sql_text(i);
    END LOOP;
  EXCEPTION
    WHEN VALUE_ERROR THEN NULL;
  END;
  v_temp := translate(v_temp,''''||chr(0)||chr(10),'"');
  track(v_temp);
end;
/

Remember to drop (or disable) the trigger when you have finished with it.

---

Related Topics