0

Is there anyway to get the input parameter values, if any, bound to the a statement that caused an error in the database from a trigger? Regardless if the statement in SQL och a procedure/function call.

I'm trying to set up a log of errors on a schema level, which works fine. But I can't find a way to log the parameter values as well, and that would really be helpfull.

So far the trigger I'm using looks like this...

CREATE OR REPLACE TRIGGER t_error
   AFTER SERVERERROR
   ON SCHEMA
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   l_sql       ora_name_list_t;

   l_sequence  NUMBER;
   l_statement CLOB;
   l_count     NUMBER;
   l_ts        TIMESTAMP (6);
BEGIN
   l_count := ora_sql_txt (l_sql);

   IF l_count >= 1 THEN
      FOR i IN 1 .. l_count LOOP
         l_statement := l_statement || l_sql (i);
      END LOOP;
   END IF;

   INSERT INTO oralog
        VALUES (
           seq_oralog.NEXTVAL,
           dbms_utility.format_call_stack,
           dbms_utility.format_error_stack,
           dbms_utility.format_error_backtrace,
           l_statement,
           SYSTIMESTAMP
        );

   COMMIT;
END t_error;
/

Thanks

user3035856
  • 1
  • 1
  • You are using `SERVERERROR`, how do you know that there would be a parameter attached everytime this trigger is fired? E.g- this trigger will fire if you try to `insert` into a table that doesn't exist and the `ora_sql_txt` would capture that, but would not capture what was inserted because that is irrelevant. `SERVERERROR` is most commonly due to an event cause by a DDL or DML on or for a DB Object and "may" not necessarily have any parameter values, if you want specific values then you should identify the table that is causing the issue and put a row level trigger there. – Anjan Biswas Nov 26 '13 at 22:36
  • Of course I know that the statement may not necessarily have any parameter, that goes without saying. But to say that any potential input data is irrelevant to a understanding the cause of an error I find patently absurd. Unfortunatly I'm not in a position that I can go and add triggers whenever I feel the need for it, which is why I wanted as much of a generalized solution as possible in order to prevent the kind of management overhead that would entail. But if it isn't possible I guess that is, as they say, that. – user3035856 Nov 27 '13 at 08:00
  • Well as it may seem absurd, it is what it is. System level triggers are more specifically designed for system events. In the example I gave, the data is irrelevant because the object "doesn't" even exist, but that is that scenario, so there could be a 100 more scenario at a system level and you have to be more specific in order to capture them. If you can add system level triggers whenever you feel the need, you can very well create table level triggers whenever you feel the need (and they would be much safe). – Anjan Biswas Nov 28 '13 at 07:52
  • I'm not sure how you got the idea that I can add a system level trigger whenever I feel the need. I said that I couldn't add a trigger whenever I needed to, without being more specific than that, becuase it involved a quite a bit of managerial overhead and was hoping for a generalized solution that could be added once. I am quite aware, to be honest, that the actual event attributes does not contain any parameter data beyond what can be found in the query. Which is why I asked if there was any way for the trigger, within reason of course, to access the parameter values. – user3035856 Nov 28 '13 at 10:39
  • I ain't sure if there is a way to extract the value of a bind variable. Even if you extract the sql_text it would be just helpful in giving u the query that is at fault. In a large enterprise system a system level trigger could either give you nothing or a lot of useless information (based in your requirement). – Anjan Biswas Nov 28 '13 at 13:38

0 Answers0