How to capture SQL generating error?

posted Apr 1, 2011, 12:38 PM by Sachchida Ojha

CREATE TABLE error_tab (error_date DATE, error_msg VARCHAR2(512), SQL VARCHAR2(4000))

 

CREATE OR REPLACE TRIGGER capture_sql_error

   AFTER SERVERERROR ON DATABASE

DECLARE

   p_sql_text   ora_name_list_t;

   p_stmt       VARCHAR2 (4000);

BEGIN

   FOR i IN 1 .. ora_sql_txt (p_sql_text)

   LOOP

      p_stmt := p_stmt || p_sql_text (i);

   END LOOP;

 

   INSERT INTO error_tab

        VALUES (SYSDATE,

                ora_server_error_msg (1),

                p_stmt

               );

END capture_sql_error;

/

Comments