Thanks for the question, Sole.
Asked: August 24, 2016 - 9:52 pm UTC
Last updated: August 25, 2016 - 4:35 am UTC
Version: 11g
Viewed 1000+ times
You Asked
I need to write a query that will extract only new characters appended in a text field. Almost like auditing but done by query.
Example
Select field1 from table1;
the dog is running in the park
user appends more data into field1 via application
the dog is eating
Select field1 from table1;
the dog is running in the park
the dog is eating
I need to be able to extract with a query the new appended characters "the dog is eating"
Thank you,
and Connor said...
If it's definitely append only, then you can compare before and after lengths with a trigger
SQL> create table t ( x varchar2(100));
Table created.
SQL>
SQL> insert into t values ('the dog is in the park');
1 row created.
SQL>
SQL> set serverout on
SQL> create or replace
2 trigger TRG
3 before update on T
4 for each row
5 begin
6 if length(:old.x) < length(:new.x) then
7 dbms_output.put_line('New data: '||substr(:new.x,length(:old.x)+1));
8 end if;
9 end;
10 /
Trigger created.
SQL> sho err
No errors.
SQL>
SQL> update t set x = x || 'the dog is eating';
New data: the dog is eating
1 row updated.