Oracle:有没有办法获取最近的SQL语法错误? [英] Oracle: Is there a way to get recent SQL syntax errors?
问题描述
我们在应用程序日志中看到很多"ORA-00936:缺少表达式"错误. Oracle中是否有一种方法可以确定哪些语句失败了?
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?
我尝试查询v $ sql,但由于这些语句未通过语法检查,因此未插入该视图中.
I tried querying v$sql, but these statements are not inserted into that view, since they don't pass the syntax checks.
我们的C#应用程序正在使用Linq生成对Oracle数据库的查询.这使得从应用程序获取sql查询有点困难.我希望我可以更轻松地从Oracle那里获得它.
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.
推荐答案
您可以在Oracle中创建一个触发器,该触发器将记录所有错误(或几乎所有错误-NO_DATA_FOUND都不被视为错误).在下面的示例中,架构中的任何错误都记录在TRACK_DETAIL表中(错误在一行中,错误的SQL在下一行中).您可以使用序列号,日期/时间等使其更复杂.
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.
这篇关于Oracle:有没有办法获取最近的SQL语法错误?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!