CLOBs are not just big varchars

We had a question on AskTom the other day, talking about comparing clobs. (Paraphrasing) the question inquired as to why we had a DBMS_LOB.COMPARE function, when you can you just compare clobs directly, using the following example:


SQL> create table T ( x clob, y clob );

Table created.

SQL> create or replace
  2  trigger TRG before insert or update on T
  3  for each row
  4  begin
  5    if :new.x != :new.y then
  6       raise_application_error(-20000,'error');
  7    end if;
  8  end;
  9  /

Trigger created.

SQL> insert into T values ('qwe','qwe');

1 row created.

SQL>
SQL> update T set y = 'abc';
update T set y = 'abc'
       *
ERROR at line 1:
ORA-20000: error
ORA-06512: at "TRG", line 3
ORA-04088: error during execution of trigger 'MCDONAC.TRG'

You can see that we’ve created a table, and added a trigger to make sure the two clob columns are equal. We’ve done what seems to be all the tests required, namely, test for equality and test for inequality. But the example masks that this is in fact a special case, namely, that we are creating/replacing a clob in its entirety. And that is not the only way we can manage clobs. Here is why you need to be super careful when using LOB’s within triggers.


SQL> declare
  2    c clob;
  3  begin
  4    select y into c from t for update;
  5    dbms_lob.writeappend(c,100,rpad('x',100,'x'));
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from T;

X
--------------------------------------------------------------------------------
Y
--------------------------------------------------------------------------------
qwe
qwexxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxx

The DBMS_LOB routines (such as “writeappend”) allow modification of a clob’s content (in fact, its the normal way of doing so). And notice that modifying the clob is not the same as updating the clob. The trigger has not fired, and hence our rule of “x” being equal to “y” is trivially broken.

2 responses to “CLOBs are not just big varchars”

Kevan Gelling

December 15, 2015 at 1:18 am

“CLOBs are not just big varchars” – but why not?

Regardless of how Oracle implement CLOBs, as a developer shouldn’t I be able to treat them as if they were just big VARCHARs and not need to remember a list of the gotchas?

Here’s another one that keeps catching me out:

> SELECT my_lob FROM my_table@db_link ;
ORA-22992: cannot use LOB locators selected from remote tables

1. connormcdonald
  
  December 20, 2015 at 3:20 pm
  
  Well… a clob *could* be up to 128TB in size. So given that just about any database access driver for any 3GL language when fetching a character string from the database does:
  
  – ask for maximum returnable size of data
  – alloc client side memory of that size
  – fetch the data
  
  I’m not sure I’d want the same treatment for clobs. My laptop doesnt have 128TB of RAM for starters 🙂