Author |
Topic: Grabbing SQL used for update (9i) (Read 2552 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
|
|
|
|