troubleshooting Question

Trigger to record SQL when constraint error encountered

Avatar of Julie Kurpa
Julie KurpaFlag for United States of America asked on
DatabasesOracle DatabaseSQL
14 Comments1 Solution125 ViewsLast Modified:
I am trying to get a trigger to work when a user gets a constraint error.    It is supposed to record the SQL being executed by the user.  

The trigger is working fine until I try to combine the multiple lines of the  SQL statement into a variable so that it's all one line.

But when I put in the  line to do the combine, only the first two utl_file.put_line commands performed prior to the loop will work.  All others fail.  

Here is my combine command:      sql_stmt_text := sql_stmt_text || l_text(i);

Here is the trigger:

create or replace trigger test_trigger
after servererror on database

declare

l_text ora_name_list_t;
l_n number;
sql_stmt_text varchar2(32767);
v_output_file1 utl_file.file_type;    

begin

v_output_file1 := UTL_FILE.FOPEN('SQL_STMT_OUTPUT','failing_SQL_Stmt.txt','W');                  


if ( is_servererror(0001) )
then
  utl_file.put_line(v_output_file1,'--------------------');
  utl_file.put_line(v_output_file1,'statement causing error: ');
 
  l_n := ora_sql_txt( l_text );

  for i in 1 .. nvl(l_n,0) loop

    -- this statement works when it is the only one in the loop.
    -- also all following utl_file.put_line commands are successful.
    utl_file.put_line(v_output_file1,l_text(i));

    -- when I include this statement, none of the utl_file.put_line commands work except
    --  for the two just before the loop.  
    sql_stmt_text := sql_stmt_text || l_text(i);

  end loop;

  utl_file.put_line(v_output_file1,sql_stmt_text);

  utl_file.put_line(v_output_file1,'error text: ');

  for i in 1 .. ora_server_error_depth
  loop
    utl_file.put_line(v_output_file1,ora_server_error_msg(i));
  end loop;
    utl_file.put_line(v_output_file1,'--------------------');

end if;

utl_file.fclose_all;

end;
/
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros