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 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....................