Author |
Topic: Grabbing SQL used for update (9i) (Read 2260 times) |
|
Pete Finnigan
PeteFinnigan.com Administrator
Oracle Security is easier if you design for it
View Profile | WWW | Email
Gender: 
Posts: 309
|
 |
Re: Grabbing SQL used for update (9i)
« Reply #1 on: Nov 1st, 2006, 10:01am » |
Quote | Modify
|
Hi Darren,
I dont have any example code but it should be possible with a trigger to look via v$sqlarea, v$sql etc (beware of trigger performance and also access to these views is expensive) and get the SQL. I seem to remember in the darkest depths of my mind seeing a similar question a few years ago. You could try searching c.d.o.s and oracle-l, lazydba and dba-village as these are the likely places I saw it. I beleive that someone did this and got it working.
cheers
Pete
|
|
 IP Logged |
Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
|
|
|
Pete Finnigan
PeteFinnigan.com Administrator
Oracle Security is easier if you design for it
View Profile | WWW | Email
Gender:
Posts: 309
|
|
Re: Grabbing SQL used for update (9i)
« Reply #2 on: Nov 6th, 2006, 1:10am » |
Quote | Modify
|
This works in 9iR2. Not sure about 9iR1.
Code:
create or replace TRIGGER log_upd before update on fred
DECLARE
v_temp VARCHAR2(2000);
v_num NUMBER;
v_sql_text ora_name_list_t;
begin
v_num := ora_sql_txt(v_sql_text);
BEGIN
FOR i IN 1..v_num LOOP
v_temp := v_temp || v_sql_text(i);
END LOOP;
EXCEPTION
WHEN VALUE_ERROR THEN NULL;
END;
v_temp := translate(v_temp,''''||chr(0)||chr(10),'"');
dbms_output.put_line(v_temp);
end;
|
|
|
|
IP Logged |
Pete Finnigan (email:pete@petefinnigan.com)
Oracle Security Web site: http://www.petefinnigan.com
Forum: http://www.petefinnigan.com/forum/yabb/YaBB.cgi
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
|
|
|
|
