Hi Tom,

I don't know if his/her review is brilliant or critical,
but it is useful because so I have been able to read about
your FBI's solution.

Thank you Tom.

Mario from Rome, Europe ;-)

How would you do a contitional update of a clob in table?  I want to update the clob, but only if it different.  Would I have to select it out of the table first and do a DBMS_LOB.COMPARE?  DB ver Oracle 9.2.0.7

Thanks..

-----------------------------------

create table t1 (one number, two clob);

Table created.

SQL> 
SQL> insert into t1 values (1, 'This is my clob1');

1 row created.

SQL> insert into t1 values (2, 'This is my clob2);

1 row created.

SQL> DECLARE
  2     l_clob   CLOB := 'This is MY clob1';
  3  BEGIN
  4     UPDATE t1
  5        SET two = l_clob
  6      WHERE one = 1
  7        AND two != l_clob;
  8  
  9     IF SQL%ROWCOUNT = 1 THEN
 10     -- Do some additional logic
 11        NULL;
 12     END IF;
 13  END;
 14  /
      AND two != l_clob;
          *
ERROR at line 7:
ORA-06550: line 7, column 11:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB
ORA-06550: line 4, column 4:
PL/SQL: SQL Statement ignored

---------------------------------------------

 

Hi Tom,
This is my first encounter with this website so please excuse me if I am not following some widely accepted standards here.

Seeing your previous output I just wanted to add some additional related question:

I have a table with mixed content including one CLOB column.
I want to write a trigger which fires on UPDATE for every row but only when anything EXCEPT one column changes.

The only way I could think of doing that was to use the when clause and list all other columns except that one in the following manner:

when (old.task_id != new.task_id OR old.task_title!=new.task_title OR ...etc)

However when using :
when (old.task_description != new.task_description) I am getting an error because this column is of CLOB datatype.

Is there any way to "detect" a change in this column or otherwise achive what I am after here?

Thanks,
Pawel.

I read the following in the 'Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 2 (9.2)' </code> http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96591/whatsnew.htm#969650 <code>

>------citation
Removed Restrictions

The following restrictions are removed in Oracle9i Release 2.

* Trigger restriction removed

There is now DML BEFORE ROW Trigger :new support for LOBs. This means that triggers on LOBs follow the same rules as triggers on any other type of column.
>----- citation end

Informal tests with a clob column in a table seem to indicate that indeed changes in the content of a clob are noticed by the trigger.

In your response you mention that this is not guaranteed.

think about how most lobs are created

a) empty or partial lob inserted
b) remaining lob data is streamed into lob, since they are big by definition

eg:

ops$tkyte%ORA10GR2> create table t ( x int, y clob );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace trigger t
  2  before insert on t for each row
  3  begin
  4          dbms_output.put_line( :new.x || ', ' || :new.y );
  5  end;
  6  /

Trigger created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          l_clob clob;
  3  begin
  4          insert into t values ( 1, empty_clob() ) returning y into l_clob;
  5          dbms_lob.writeAppend( l_clob, length('hello world'), 'hello world' );
  6  end;
  7  /
1,

PL/SQL procedure successfully completed.


the trigger fires for the insert, but won't fire on subsequent "lob operations"


so, triggers that access lobs are virtually useless.