-----Original Message----- From: Richard Pascual Sent: ٢٠١١/٠٨/١٦, ٣:٣٥ ص To: oracle-plsql@googlegroups.com Subject: Re: [PL/SQL] Re: Getting Script auotmatically
thanks and best regards Thinking a little laterally, have you considered using Oracle's Log Miner? It's a feature that is available since Oracle 9i. I am also experimenting with the log miner feature so I don't have much more advice other than the link I am using for my testing: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/logminer.htm You can query Oracle's redo logs to detect the SQL DML actually issued against specific database tables by user and also by the timestamp when they did the change. I think this would make your task easier since you can capture the exact SQL issued by the user instead of trying to reverse engineer it by conducting a data diff against the table before and after the DML was issued. For example: Database user SMITH issues the following command to update the employees table: UPDATE HR.EMPLOYEES SET NAME = 'Jonathan' WHERE EMP_ID = 12345; The REDO Logs contain this exact statement once it is issued. No need to guess, you can access it by querying through log miner. One thing that may help expedite your "search" through the log miner application is if you know the timestamp of when the statement was issued and the user who issued it. THAT can be easily captured in a "watch list" table populated by your original DML trigger on the table. There are a few administrative conventions that you need to set up to make this work, but it may help you make more efficient work of your task at hand. Does anyone in our group have any experience with log miner or any further tips and caveats to help us accomplish this task? Just a few pointers to help you get started... Richard On Thu, Aug 11, 2011 at 2:13 PM, Yuvaraj Sundaresan < yuvarajssundare...@gmail.com> wrote: > This is right but i need which column is changed only that column should > available in update statement. > > For Eg: In a employee table name, emp_id, age, sex are available columns > and when i updating name then my update statement should be > update employee > set name = :new.name > where emp_id = :new.emp_id > > > On Tue, Aug 9, 2011 at 8:00 PM, ddf <orat...@msn.com> wrote: > >> >> >> On Aug 8, 9:58 am, Yuvaraj Sundaresan <yuvarajssundare...@gmail.com> >> wrote: >> > Dear friends, >> > I need one urgent help, my requirement is one table many people will >> > update at the end of the day i need all the update script of that table. >> So >> > i used the trigger to build the update statement dynamically but its not >> > working see the below coding and help to proceed. In this coding after >> > execute immediate its not working. >> > >> > create or replace trigger TEST >> > after insert OR UPDATE OR DELETE on person_table REFERENCING OLD AS >> OLD >> > NEW AS NEW >> > for each row >> > declare >> > SQL1 VARCHAR2(4000); >> > SQL2 VARCHAR2(4000); >> > SQL3 VARCHAR2(4000); >> > SQL4 VARCHAR2(4000); >> > begin >> > IF INSERTING THEN >> > NULL; >> > ELSIF UPDATING THEN >> > SQL1 := 'UPDATE person_table '; >> > FOR I IN (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME >> = >> > 'PERSON_TABLE') LOOP >> > DBMS_OUTPUT.PUT_LINE(1); >> > EXECUTE IMMEDIATE('SELECT :OLD.'||I.COLUMN_NAME||' FROM DUAL') >> INTO >> > SQL2; >> > DBMS_OUTPUT.PUT_LINE(2); >> > EXECUTE IMMEDIATE('SELECT :NEW.'||I.COLUMN_NAME||' FROM DUAL') >> INTO >> > SQL3; >> > IF SQL2 <> SQL3 THEN >> > IF SQL4 IS NOT NULL THEN >> > SQL4 := SQL4 ||' , '||I.COLUMN_NAME||' = '||SQL3; >> > ELSE >> > SQL4 := SQL4 ||' , '||I.COLUMN_NAME||' = '||SQL3; >> > END IF; >> > END IF; >> > END LOOP; >> > DBMS_OUTPUT.PUT_LINE(3); >> > SQL1 := SQL1 || SQL4 ||' WHERE NAME = '||:NEW.NAME; >> > DBMS_OUTPUT.PUT_LINE(4||'--'||SQL1); >> > INSERT INTO MY_SQL VALUES (SQL1); >> > ELSIF DELETING THEN >> > NULL; >> > END IF; >> > end TEST; >> >> What, exactly, are you trying to do with this? The error I see is >> >> ERROR at line 1: >> ORA-01008: not all variables bound >> ORA-06512: at "BING.TEST", line 14 >> ORA-04088: error during execution of trigger 'BING.TEST' >> >> because of the selects against DUAL you're trying to make >> dynamically. Your trigger can be reduced to: >> >> SQL> create or replace trigger TEST >> 2 after insert OR UPDATE OR DELETE on person_table -- REFERENCING >> OLD AS OLD NEW AS NEW >> 3 for each row >> 4 declare >> 5 SQL1 VARCHAR2(4000); >> 6 SQL2 VARCHAR2(4000); >> 7 SQL3 VARCHAR2(4000); >> 8 SQL4 VARCHAR2(4000); >> 9 begin >> 10 IF INSERTING THEN >> 11 NULL; >> 12 ELSIF UPDATING THEN >> 13 SQL1 := 'UPDATE person_table set yarp = '||:new.yarp||', >> znip = '''||:new.znip||''', pling = '''||:new.pling||''' where name = >> '''||:new.name||''''; >> 14 DBMS_OUTPUT.PUT_LINE(1); >> 15 DBMS_OUTPUT.PUT_LINE(2); >> 16 DBMS_OUTPUT.PUT_LINE(3); >> 17 DBMS_OUTPUT.PUT_LINE('4 --'||SQL1); >> 18 INSERT INTO MY_SQL VALUES (SQL1); >> 19 ELSIF DELETING THEN >> 20 NULL; >> 21 END IF; >> 22 end TEST; >> 23 / >> >> Trigger created. >> >> SQL> >> SQL> show errors >> No errors. >> SQL> >> SQL> set serveroutput on size 1000000 >> SQL> >> >> And it will function correctly: >> >> SQL> insert into person_table >> 2 values(1, 'Bleebo','Tran', sysdate); >> >> 1 row created. >> >> SQL> >> SQL> update person_table >> 2 set name = 'Roopwinger' where yarp = 1; >> 1 >> 2 >> 3 >> 4 --UPDATE person_table set yarp = 1, znip = 'Tran', pling = '09- >> AUG-11' where name = 'Roopwinger' >> >> 1 row updated. >> >> SQL> >> SQL> select * from my_sql; >> >> SQLTXT >> >> ------------------------------------------------------------------------------------------------------------------------------------ >> UPDATE person_table set yarp = 1, znip = 'Tran', pling = '09-AUG-11' >> where name = 'Roopwinger' >> >> SQL> >> >> >> >> David Fitzjarrell >> >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to Oracle-PLSQL@googlegroups.com >> To unsubscribe from this group, send email to >> oracle-plsql-unsubscr...@googlegroups.com >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en >> > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
