Maintaining Transaction logs on tables in Oracle 8.0.4....

  I have an application which contains a data table, this table has a similarly defined transaction table.

CREATE TABLE mydata
(

  cID NUMBER(10,0),
.....
  cChanges BLOB
)

CREATE TABLE mydata_Trans
(

  cID NUMBER(10,0),
.....
  cChanges BLOB,
  cRevisionID NUMBER(10,0)
)

I have ON INSERT, ON UPDATE and ON DELETE triggers on the mydata table which maintain the transaction log - inserting new values into the myData_trans table.

cChanges is a BLOB field into which users enter text which states what the reason for the data changes between records were. It could be quite large
(though probably < 16k) in length so I can't use VARCHAR2 because of the
risk of running out of space.

My problem is that I get ORA-22275 Invalid Blob Locator errors in my myData table triggers when trying to copy data via INSERT statements into the Transaction table :

INSERT INTO myData_Trans (cID,..,cChanges) VALUES
(:new.cID,...,:new.cChanges);

I can't be the only person who has ever wanted to build this kind of functionality I really would appreciate any and all help anyone can give me with this.

• Ian.