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

Question

We see a lot of "ORA-00936: missing expression" errors in our application log. Is there a way in Oracle to determine which statements are not being executed?

I tried querying v $ sql, but these statements are not inserted into this view, since they do not pass syntax checks.

Our C # application uses Linq to generate a query into an Oracle database. This makes it difficult to get the sql query from the application. I was hoping I could just get this from Oracle.

Answer 1

Oracle, ( - NO_DATA_FOUND ). TRACK_DETAIL ( , SQL ). , / ..

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;
/

( ) , .

Answer 2

- sql ( sql_trace = true), .

Answer 3

- Wireshark , Oracle, , SQL . , , .

Answer 4

Try the SQL monitoring solution from the Kris Vandermotten blog .

You can also redirect the log with the DataContext.Log property :

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

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