Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Trigger to record SQL when constraint error encountered
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_O
if ( is_servererror(0001) )
then
utl_file.put_line(v_output
utl_file.put_line(v_output
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
-- 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
utl_file.put_line(v_output
for i in 1 .. ora_server_error_depth
loop
utl_file.put_line(v_output
end loop;
utl_file.put_line(v_output
end if;
utl_file.fclose_all;
end;
/
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_O
if ( is_servererror(0001) )
then
utl_file.put_line(v_output
utl_file.put_line(v_output
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
-- 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
utl_file.put_line(v_output
for i in 1 .. ora_server_error_depth
loop
utl_file.put_line(v_output
end loop;
utl_file.put_line(v_output
end if;
utl_file.fclose_all;
end;
/
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What version of Oracle are you using?
Works for me using 18.0.
I modified your PUT_LINES to add location1 and location2 tothem so I know what wrote what.
My test run output:
My file contents:
Works for me using 18.0.
I modified your PUT_LINES to add location1 and location2 tothem so I know what wrote what.
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,'Location1: ' || 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,'Location2: ' || 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;
/
show errors
create or replace directory SQL_STMT_OUTPUT as 'C:\TEMP';
drop table tab1 purge;
create table tab1(col1 char(1) primary key);
insert into tab1 values('a');
insert into tab1 values('a');
My test run output:
SQL> @q
Trigger created.
No errors.
Directory created.
Table dropped.
Table created.
1 row created.
insert into tab1 values('a')
*
ERROR at line 1:
ORA-00001: unique constraint (BUD.SYS_C0016150) violated
My file contents:
C:\TEMP>type failing_SQL_Stmt.txt
--------------------
statement causing error:
Location1: insert into tab1 values('a')
Location2: insert into tab1 values('a')
error text:
ORA-00001: unique constraint (MYUSER.SYS_C0016150) violated
--------------------
Thanks Slightwv. :)
I'm using Oracle 11g running on Oracle LInux.
Perhaps it would help if I included the output without the "offending" line and then with it so you see what I see. Am putting it together now.
I'm using Oracle 11g running on Oracle LInux.
Perhaps it would help if I included the output without the "offending" line and then with it so you see what I see. Am putting it together now.
In breaking down the changes, I see that it's actually the "utl_file.put_line(v_outpu
Here is the output WITHOUT the "utl_file.put_line(v_outpu
--------------------
statement causing error:
INSERT INTO myuser.tab1(column1,column
column6,column7,column8,co
m13) VALUES (1111,2222,3333,4444,5555,
111,1212,1313)
error text:
ORA-00001: unique constraint (MYUSER.PK_TAB1) violated
This is the output when I include the "utl_file.put_line(v_outpu
--------------------
statement causing error:
INSERT INTO myuser.tab1(column1,column
column6,column7,column8,co
m13) VALUES (1111,2222,3333,4444,5555,
111,1212,1313)
I tried sending the contents of variable sql_stmt_text to different file altogether.
When doing that, I get the full constraint information written to the first location but never the content of the sql_stmt_text variable in the 2nd file. The 2nd location stays blank.
Here is the output WITHOUT the "utl_file.put_line(v_outpu
--------------------
statement causing error:
INSERT INTO myuser.tab1(column1,column
column6,column7,column8,co
m13) VALUES (1111,2222,3333,4444,5555,
111,1212,1313)
error text:
ORA-00001: unique constraint (MYUSER.PK_TAB1) violated
This is the output when I include the "utl_file.put_line(v_outpu
--------------------
statement causing error:
INSERT INTO myuser.tab1(column1,column
column6,column7,column8,co
m13) VALUES (1111,2222,3333,4444,5555,
111,1212,1313)
I tried sending the contents of variable sql_stmt_text to different file altogether.
When doing that, I get the full constraint information written to the first location but never the content of the sql_stmt_text variable in the 2nd file. The 2nd location stays blank.
I can only go with what I can personally test. The code I posted worked for me.
If you want me to run some other test, post the code you wish me to test. I don't have access to an 11g database any more but can test on 12.2 right now and 18c later today.
>>Here is the output WITHOUT the "utl_file.put_line(v_outpu
If you are getting the output you want, what are you trying to accomplish by changing it? I'm not understanding why you need the SQL on a single line. What happens when you get SQL larger than 32k?
If you want me to run some other test, post the code you wish me to test. I don't have access to an 11g database any more but can test on 12.2 right now and 18c later today.
>>Here is the output WITHOUT the "utl_file.put_line(v_outpu
If you are getting the output you want, what are you trying to accomplish by changing it? I'm not understanding why you need the SQL on a single line. What happens when you get SQL larger than 32k?
It seems your code is exactly like mine so I don't really have any changes to make. If it runs in 12.2 then I'm not sure what else to try.
I wanted to get the SQL into a single line because I plan to extract certain elements from it and use it in a procedure. If it's wrapping like it is, then certain elements I need may be split between lines.
I wanted to get the SQL into a single line because I plan to extract certain elements from it and use it in a procedure. If it's wrapping like it is, then certain elements I need may be split between lines.
Took me a while to remember this: The FOPEN has a max_linesize parameter that defaults to 1024.
You need to specify a larger value:
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/UTL_FILE.html#GUID-DF14ADC3-983D-4E0F-BE2C-60733FF58539
I also cleaned up the code. There isn't a reason to create the concatenated string. Just use PUT instead of PUT_LINE. Just issue a PUT_LINE outside the loop to finish off the line.
Here is my modified test case:
You need to specify a larger value:
https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/UTL_FILE.html#GUID-DF14ADC3-983D-4E0F-BE2C-60733FF58539
I also cleaned up the code. There isn't a reason to create the concatenated string. Just use PUT instead of PUT_LINE. Just issue a PUT_LINE outside the loop to finish off the line.
Here is my modified test case:
create or replace trigger test_trigger
after servererror on database
declare
l_text ora_name_list_t;
l_n number;
v_output_file1 utl_file.file_type;
begin
v_output_file1 := UTL_FILE.FOPEN('SQL_STMT_OUTPUT','failing_SQL_Stmt.txt','W',32767);
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
utl_file.put(v_output_file1,l_text(i));
end loop;
utl_file.put_line(v_output_file1,null);
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(v_output_file1);
end;
/
show errors
create or replace directory SQL_STMT_OUTPUT as '/tmp';
drop table tab1 purge;
create table tab1(col1 varchar2(4000) primary key);
insert into tab1 values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
insert into tab1 values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
I really appreciate your help.
On a whim, I directed that variable to a table instead of to a file and it seems to work. The entire SQL is being written into one column. This is where I eventually wanted to end up but was just writting to the file so I could see what I was getting.
Here's the adjusted code.
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_O
if ( is_servererror(0001) )
then
utl_file.put_line(v_output
utl_file.put_line(v_output
l_n := ora_sql_txt( l_text );
for i in 1 .. nvl(l_n,0) loop
utl_file.put_line(v_output
sql_stmt_text := sql_stmt_text || l_text(i);
end loop;
INSERT INTO myuser.sql_event VALUES (sql_stmt_text);
utl_file.put_line(v_output
for i in 1 .. ora_server_error_depth
loop
utl_file.put_line(v_output
end loop;
utl_file.put_line(v_output
end if;
utl_file.fclose_all;
end;
/
On a whim, I directed that variable to a table instead of to a file and it seems to work. The entire SQL is being written into one column. This is where I eventually wanted to end up but was just writting to the file so I could see what I was getting.
Here's the adjusted code.
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_O
if ( is_servererror(0001) )
then
utl_file.put_line(v_output
utl_file.put_line(v_output
l_n := ora_sql_txt( l_text );
for i in 1 .. nvl(l_n,0) loop
utl_file.put_line(v_output
sql_stmt_text := sql_stmt_text || l_text(i);
end loop;
INSERT INTO myuser.sql_event VALUES (sql_stmt_text);
utl_file.put_line(v_output
for i in 1 .. ora_server_error_depth
loop
utl_file.put_line(v_output
end loop;
utl_file.put_line(v_output
end if;
utl_file.fclose_all;
end;
/
That worked great slighwv! You are wonderful!
I set the FOPEN size to 32767 and I'm getting the full SQL text on one line in the file.
Now I have many options on how to handle the rest of my project.
I set the FOPEN size to 32767 and I'm getting the full SQL text on one line in the file.
Now I have many options on how to handle the rest of my project.
Here's the working product with the writing of the SQL on all one line to the output file outside the loop.
create or replace trigger test_trigger
after servererror on database
declare
l_text ora_name_list_t;
l_n number;
sql_stmt_text varchar2(10000);
v_output_file1 utl_file.file_type;
begin
v_output_file1 := UTL_FILE.FOPEN('SQL_STMT_O
if ( is_servererror(0001) )
then
utl_file.put_line(v_output
utl_file.put_line(v_output
l_n := ora_sql_txt( l_text );
for i in 1 .. nvl(l_n,0) loop
utl_file.put_line(v_output
sql_stmt_text := sql_stmt_text || l_text(i);
end loop;
-- write the combined SQL to a table
INSERT INTO myuser.sql_event VALUES (sql_stmt_text);
-- write the combined SQL to the file
utl_file.put_line(v_output
for i in 1 .. ora_server_error_depth
loop
utl_file.put_line(v_output
end loop;
utl_file.put_line(v_output
end if;
utl_file.fclose_all;
end;
/
create or replace trigger test_trigger
after servererror on database
declare
l_text ora_name_list_t;
l_n number;
sql_stmt_text varchar2(10000);
v_output_file1 utl_file.file_type;
begin
v_output_file1 := UTL_FILE.FOPEN('SQL_STMT_O
if ( is_servererror(0001) )
then
utl_file.put_line(v_output
utl_file.put_line(v_output
l_n := ora_sql_txt( l_text );
for i in 1 .. nvl(l_n,0) loop
utl_file.put_line(v_output
sql_stmt_text := sql_stmt_text || l_text(i);
end loop;
-- write the combined SQL to a table
INSERT INTO myuser.sql_event VALUES (sql_stmt_text);
-- write the combined SQL to the file
utl_file.put_line(v_output
for i in 1 .. ora_server_error_depth
loop
utl_file.put_line(v_output
end loop;
utl_file.put_line(v_output
end if;
utl_file.fclose_all;
end;
/
Premium Content
You need an Expert Office subscription to comment.Start Free Trial