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
v_output_file1 := UTL_FILE.FOPEN('SQL_STMT_OUTPUT','failing_SQL_Stmt.txt','W');
if ( is_servererror(0001) )
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.
-- 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);
utl_file.put_line(v_output_file1,'error text: ');
for i in 1 .. ora_server_error_depth