RE: Problems with LOBs and triggers

From: Shaw, Glen <Glen.Shaw_at_BellSouth.com>
Date: Tue, 7 Nov 2000 08:11:42 -0500
Message-Id: <10673.121287@fatcity.com>

Helmut,

The problem is the requirement of having to have a lock on the row for the LOB column to be updated. There is a workaround that I think might help you. I posted it to the list a few days ago. I reprint it here. What you need to do is use a temporary lob locator through the dbms_lob package. Take a look at the documentation on the DBMS_LOB package. Below is an example using an insert.

Ex.
  declare
    tmpCLOB CLOB;
    sData VarChar2(10000) := 'Some really long string that is more than 4k.';
  begin

Loc Cache dbms_lob.createtemporary(tmpCLOB, TRUE, 10);
Loc length offset string dbms_lob.write(tmpCLOB, Length(sData), 1, sData); insert into foo (foo_id, foo_notes) values (foo_seq.nextval, tmpCLOB); commit; end; /

The really cool thing about using the temporary lobs is that you can do updates to lobs without having to lock the row beforehand. This is a really good thing if you are working in java and already have the lob value in an object element.

Hope this helps,
Glen

-----Original Message-----
From: Helmut Daiminger [mailto:hdaiminger_at_vivonet.com] Sent: Monday, November 06, 2000 7:09 PM
To: Sawyer Joey; Oracle List (Telelist); Oracle DBA List (Lazy DBA) Subject: Problems with LOBs and triggers Importance: High

Hi!

I'm getting a strange error message here. Seems like you are not allowed to update LOB values in a trigger.

problem cause: Triggers not allowed to update LOBs problem symptom: changes made to tables containing LOBs are rejected, and Oracle returns the following error: "ORA-22275 invalid LOB locator specified"

The documentation simply tells you to get rid of the trigger. But there must be a way to manipulate LOB values in a trigger.

Any idea?

This is 8.1.6 on Win2k.

Thanks,
Helmut

Think you know someone who can answer the above question? Forward it to them!
to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com Received on Tue Nov 07 2000 - 07:11:42 CST