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

We are seeing a lot of "ORA-00936: missing expression" errors in our application log. Is there a way in Oracle to determine what statement(s) are failing?

I tried querying v$sql, but these statements开发者_运维百科 are not inserted into that view, since they don't pass the syntax checks.

Our C# application is using Linq to generate a query to an Oracle database. This makes it a bit difficult to get the sql query from the application. I was hoping I could just get it from Oracle easier.

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.

If you can enable sql tracing from the application code somehow (alter session set sql_trace=true), the statements will show up in the trace files on the database host.

You could try using something like Wireshark on the port used to connect to Oracle to see what SQL statements are being sent. Might not be the best answer--but it might get you where you need to go quicker.

Try SQL monitoring solution from Kris Vandermotten blog.

Also you can redirect log with DataContext.Log property :

using (NorthwindDataContext context = new NorthwindDataContext())
{
  context.Log = Console.Out;
}

Or use any other debugging tools like LInQ to Entities Visualizer ...