Zope/Oracle: Using CLOB and BLOB columns in Zope

Oracle provides two datatypes to handle large (> 4000 bytes) columns. These datatypes are CLOB (Character Large Object) and BLOB (Binary Large Object).

Take the following table:

create table my_clobs (
  row_id INTEGER,
  data   CLOB
);

Dealing with LOBS is not as straightforward as dealing with a normal column type. First, LOB columns cannot be used in a WHERE, GROUP BY, or ORDER BY clause.

Inserting a CLOB

It is not possible to insert data into CLOB when the row is created. It must be inserted and then updated.

insert into my_clobs (
  row_id,
  data
) values (
  1,
  empty_clob()
)

Updating a CLOB

Updating a CLOB is a two-part process, first the row must be SELECTed FOR UPDATE and then updated via a Python script.

SQL Method selectClobForUpdate

select
  data
from
  my_clobs
where
  row_id = 1 
for update

Python script

text = 'some text'

for row in container.selectClobForUpdate():
  row.data.write(text)
  row.data.trim(len(text))