is there any way to log all failed sql statements in oracle 10g

is there any way to log all failed sql statements in oracle 10g to a table or file?

By failed I mean bad formated sql statement or sql statements that do not have permission for a table or object.

From stackoverflow

• You can do this with a system trigger.

  I directly copied this code from http://www.psoug.org/reference/system_trigger.html.

  CREATE TABLE servererror_log (
  error_datetime  TIMESTAMP,
  error_user      VARCHAR2(30),
  db_name         VARCHAR2(9),
  error_stack     VARCHAR2(2000),
  captured_sql    VARCHAR2(1000));
  
  
  
  
  
   CREATE OR REPLACE TRIGGER log_server_errors
      AFTER SERVERERROR
      ON DATABASE
      DECLARE
       captured_sql VARCHAR2(1000); 
      BEGIN
        SELECT q.sql_text
        INTO captured_sql
        FROM gv$sql q, gv$sql_cursor c, gv$session s
        WHERE s.audsid = audsid
        AND s.prev_sql_addr = q.address
        AND q.address = c.parent_handle;
  
        INSERT INTO servererror_log
        (error_datetime, error_user, db_name,
         error_stack, captured_sql)
        VALUES
        (systimestamp, sys.login_user, sys.database_name,
        dbms_utility.format_error_stack, captured_sql);
      END log_server_errors;
      /
  

• Rather than hit the system views, as in Demge's answer, there is an ora_sql_txt function that gives the relevant statement.

  create or replace TRIGGER log_err after servererror on schema
  DECLARE
    v_stack VARCHAR2(2000) := substr(dbms_utility.format_error_stack,1,2000);
    v_back VARCHAR2(2000);-- := substr(dbms_utility.format_error_backtrace,1,2000);
    v_num NUMBER;
    v_sql_text ora_name_list_t;
    procedure track(p_text) is
    begin
       insert into .... values (p_text);
    end;
  begin
    v_stack := translate(v_stack,'''','"');
    track(v_stack);
    v_back := translate(v_back,'''','"');
    if v_back is not null then track(v_back); end if;
    v_num  := ora_sql_txt(v_sql_text);
    BEGIN
      FOR i IN 1..v_num LOOP
        track(to_char(i,'0000')||':'||v_sql_text(i));
      END LOOP;
    EXCEPTION
      WHEN VALUE_ERROR THEN NULL;
    END;
  end;
  

  In my own environment, I actually have 'TRACK' as a separate procedure that uses an autonomous transaction, rather than a block as above.

  create or replace procedure track (p_text IN VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    cursor c_user is
      select   sys_context('USERENV','CLIENT_INFO')       client_info,
               sys_context('USERENV','CURRENT_SCHEMA')    curr_schema,
               sys_context('USERENV','CURRENT_USER')      curr_user,
               sys_context('USERENV','DB_NAME')           db_name,
               sys_context('USERENV','HOST')              host,
               sys_context('USERENV','IP_ADDRESS')        ip,
               sys_context('USERENV','OS_USER')           osuser,
               sys_context('USERENV','SESSIONID')         sessid,
               sys_context('USERENV','SESSION_USER')      sess_user,
               sys_context('USERENV','TERMINAL')          terminal
    from dual;
    user_rec c_user%rowtype;
    v_mod  VARCHAR2(48);
    v_act  VARCHAR2(32);
    v_cli_info varchar2(64);
  begin
    open c_user;
    fetch c_user into user_rec;
    close c_user;
    DBMS_APPLICATION_INFO.READ_MODULE (v_mod, v_act);
    --DBMS_APPLICATION_INFO.READ_CLIENT_INFO(v_cli_info);
    insert into track_detail
      (id, track_time, detail, client_info, curr_schema, curr_user, db_name, 
       host, ip, osuser, sessid, sess_user, terminal, module, action)
    values (track_seq.nextval, systimestamp, p_text,
            user_rec.client_info, user_rec.curr_schema, user_rec.curr_user, 
            user_rec.db_name,     user_rec.host,        user_rec.ip, 
            user_rec.osuser,      user_rec.sessid,      user_rec.sess_user, 
            user_rec.terminal,    v_mod,                v_act);
    commit;
  end;
  

• You may want to use Auditing like:

  AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;

  By ACCESS is for each statement (which seems like what you want). By SESSION would record one record per session (high volume environment).

  Oracle's built in auditing has less overhead then a trigger. A trigger, like above, allows you to log the exact information you want. Auditing will also only catch hits on existing objects. If someone selects on a non-existent table (misspelled or whatnot) auditing will not catch it. The triggers above will.

  A lot more info in the security guide: http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/auditing.htm#i1011984

  Nick Pierpoint : +1 in an attempt to get this (correct) answer higher up the list :) - if Tom K sees this answer at the bottom with lots of "use a trigger" answers higher up we'll never hear the end of it. :)