changing lobs with select for update and dbms_lob.write

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 2 Jun 2005 12:36:27 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262119FCBE@irvmbxw02>

I have a table containing a LOB. The LOB is being changed by doing a "select for update" and a "dbms_lob.write" in PL/SQL. I notice that the value of the LOB is changed but no trigger is fired. Any ideas on how to track the LOB change through a trigger or some other mechanism? I tried this in Oracle 8.0, 8.1, 9.2, 10.1: same behaviour in every version.

I think it's counter-intuitive that you could change a database column after a "select for update" but without an update command.

See below for script and sample run.

sample run:

SQL> drop table t ;
Table supprimée.
SQL> create table t (n number, c clob) ; Table créée.
SQL> insert into t (n, c) values (1, 'ABC') ; 1 ligne créée.
SQL> commit ;
Validation effectuée.

SQL> create trigger t_b4d
2 before delete on t
3 begin

  4     dbms_output.put_line ('*************') ;
  5     dbms_output.put_line (' delete on T') ;
  6     dbms_output.put_line ('*************') ;

  7 end ;
  8 /
Déclencheur créé.
SQL> create trigger t_b4i
  2 before insert on t
  3 begin

  4     dbms_output.put_line ('*************') ;
  5     dbms_output.put_line (' insert on T') ;
  6     dbms_output.put_line ('*************') ;

  7 end ;
  8 /
Déclencheur créé.
SQL> create trigger t_b4u
  2 before update on t
  3 begin

  4     dbms_output.put_line ('*************') ;
  5     dbms_output.put_line (' update on T') ;
  6     dbms_output.put_line ('*************') ;

  7 end ;
  8 /
Déclencheur créé.
SQL> create trigger t_b4dr
  2 before delete on t
  3 for each row
  4 begin

  5     dbms_output.put_line ('+++++++++++++') ;
  6     dbms_output.put_line (' for each row trigger') ;
  7     dbms_output.put_line (' delete on T') ;
  8     dbms_output.put_line ('+++++++++++++') ;

  9 end ;
10 /
Déclencheur créé.
SQL> create trigger t_b4ir
  2 before insert on t
  3 begin

  4     dbms_output.put_line ('+++++++++++++') ;
  5     dbms_output.put_line (' for each row trigger') ;
  6     dbms_output.put_line (' insert on T') ;
  7     dbms_output.put_line ('+++++++++++++') ;

  8 end ;
  9 /
Déclencheur créé.
SQL> create trigger t_b4ur
  2 before update on t
  3 begin

  4     dbms_output.put_line ('+++++++++++++') ;
  5     dbms_output.put_line (' for each row trigger') ;
  6     dbms_output.put_line (' update on T') ;
  7     dbms_output.put_line ('+++++++++++++') ;

8 end ;
9 /
Déclencheur créé.

SQL> set serveroutput on size 2000
SQL> select * from t ;

N C

--------- --------------------------------------------------------------------------------
        1 ABC

SQL> update t set c = 'ABD' where n = 1 ; +++++++++++++
for each row trigger
update on T
+++++++++++++

update on T

1 ligne mise à jour.
SQL> commit ;
Validation effectuée.

SQL> select * from t ;

N C

--------- --------------------------------------------------------------------------------
        1 ABD
 
SQL> declare
  2     temp_lob clob ;
  3  begin
  4     select c into temp_lob
  5      from t
  6      where n = 1
  7      for update ;
  8     dbms_lob.write (lob_loc => temp_lob,
  9                     amount => 1,
 10                     offset => 3,
 11                     buffer => 'E') ;
 12     commit ;

13 end ;
14 /
Procédure PL/SQL terminée avec succès.

SQL> select * from t ;

N C

--------- --------------------------------------------------------------------------------
        1 ABE

SQL> script:

drop table t ;
create table t (n number, c clob) ;
insert into t (n, c) values (1, 'ABC') ; commit ;
create trigger t_b4d
before delete on t
begin

   dbms_output.put_line ('*************') ;
   dbms_output.put_line (' delete on T') ;
   dbms_output.put_line ('*************') ;

end ;
/

create trigger t_b4i
before insert on t
begin

   dbms_output.put_line ('*************') ;
   dbms_output.put_line (' insert on T') ;
   dbms_output.put_line ('*************') ;

end ;
/

create trigger t_b4u
before update on t
begin

   dbms_output.put_line ('*************') ;
   dbms_output.put_line (' update on T') ;
   dbms_output.put_line ('*************') ;

end ;
/

create trigger t_b4dr
before delete on t
for each row
begin

   dbms_output.put_line ('+++++++++++++') ;
   dbms_output.put_line (' for each row trigger') ;
   dbms_output.put_line (' delete on T') ;
   dbms_output.put_line ('+++++++++++++') ;

end ;
/

create trigger t_b4ir
before insert on t
begin

   dbms_output.put_line ('+++++++++++++') ;
   dbms_output.put_line (' for each row trigger') ;
   dbms_output.put_line (' insert on T') ;
   dbms_output.put_line ('+++++++++++++') ;

end ;
/

create trigger t_b4ur
before update on t
begin

   dbms_output.put_line ('+++++++++++++') ;
   dbms_output.put_line (' for each row trigger') ;
   dbms_output.put_line (' update on T') ;
   dbms_output.put_line ('+++++++++++++') ;

end ;
/

set serveroutput on size 2000
select * from t ;
update t set c = 'ABD' where n = 1 ;
commit ;
select * from t ;
declare

temp_lob clob ;
begin

   select c into temp_lob
    from t
    where n = 1
    for update ;
   dbms_lob.write (lob_loc => temp_lob,

                   amount => 1,
                   offset => 3,
                   buffer => 'E') ;

commit ;
end ;
/

select * from t ;

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 02 2005 - 15:41:43 CDT

changing lobs with select for update and dbms_lob.write - no triggers are fired?