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;
/
Works for me using 18.0.
I modified your PUT_LINES to add location1 and location2 tothem so I know what wrote what.
Open in new window
My test run output:
Open in new window
My file contents:
Open in new window