Hi ALL OracleExperts,

I have a interesting & complicated situation for myself & I need help ASAP.

Let me describe to you step by step.
I have a table which has CLOB & BLOB datatype in Oracle 8.1.7 & I am using JDBC 2.0 for my Java application.

I created a table like :

Create table ClobBlobTest (x varchar2(30),b BLOB,c CLOB);

Now if I insert 1 row into it as :

insert into ClobBlobTest values ('one','1010101010101010101010101', 'onetwothreefour');

There are no issues.
BUT THE PROBLEM START HERE AFTER.

My application wants to have a TRIGGER enforced on the table described above for INSERT/UPDATE/DELETE .This trigger would insert the old & new values for add & update into another table which I have created,say DuplicateLOB Table.

I create the TRIGGER & DuplicateLOB table where the triggers inserts data is as follows:---

create table DuplicateLOB (old_X VARCHAR2(30),old_B BLOB, old_C CLOB, new_X VARCHAR2(30), new_B BLOB, new_C CLOB);

Trigger:

CREATE OR REPLACE TRIGGER ClobBlobTest_trig BEFORE INSERT OR UPDATE ON ClobBlobTest FOR EACH ROW DECLARE command VARCHAR2(12) BEGIN IF INSERTING THEN command := 'ins';ELSIF UPDATING THEN command := 'upd'; END IF;insert into DuplicateLOB values (ld.X, ld.B, ld.C, :new.X, :new.B, :new.C); END;

PLEASE DONOT STRESS ON THE SYNTAX OF THE TRIGGER.IT'S ABSOLUTELY OK.IGNORE PARAMETERS LIKE param & use which come within the Java application.

MY PROBLEM ARISES here...........
The moment you try to insert the same record (as I have done above successfully)

insert into ClobBlobTest values ('one','1010101010101010101010101', 'onetwothreefour');.

I get an ORA-22275 :invalid LOB locator specified error.
I am sure the problem lies in updating a row of CLOB & BLOB datatype through the trigger,but if I can add a record without a trigger than why not after having a trigger ???

JDBC Developer’s Guide and Reference suggests to use EMPTY_CLOB() and EMPTY_LOB() but again they work fine when you don't have the trigger.How do I support Trigger for my purpose of add & update)


Do reply back ,I am relying on every oracle expert out there....................

Regards,
Avi