PL/SQL Dev + CLOBs + Triggers gotcha

[deanpm]

Dear All,

I have a table containing a CLOB column on which there is a trigger that fires on the AFTER UPDATE event.

The trigger inserts a copy of the ld clob column and a copy of the :new clob column into an audit log table. If I update the clob value using code, the old and new values are logged as expected. If however I update the clob column using the PL/SQL developer grid view, the new value recorded by my trigger is always null.

Whilst trying to debug this "problem" I managed to generate a run time error in my trigger which (luckily) allowed me to see the error stack;

update
set DESCRIPTION = empty_clob()
where rowid = lsqldev_rowid
returning DESCRIPTION into :v7

The stack implies that my trigger is logging a null value for :new because PL/SQL dev is setting DESCRIPTION to empty_clob() (presumably so it can then copy the new contents into the column). Perhaps the devs could confirm this point.

So, be warned, using PL/SQL Dev to update CLOB columns which happen to contain AFTER UPDATE Triggers can result in unexpected behaviour.

--
Dean

[This message has been edited by deanpm (edited 01 September 2003).]

 

[Marco Kalter]

PL/SQL Developer will indeed post an empty_clob() or empty_blob() first, and subsequently write the data. Version 6.0 will immediately post the LOB data if you are using Net 8.1 or later. On Net 8.0 this is not possible because it does not support temporary LOB's, which is required.

------------------
Marco Kalter
Allround Automations

 

[deanpm]

Originally posted by mkalter:
PL/SQL Developer will indeed post an empty_clob() or empty_blob() first, and subsequently write the data. Version 6.0 will immediately post the LOB data if you are using Net 8.1 or later. On Net 8.0 this is not possible because it does not support temporary LOB's, which is required.

Thanks for the confirmation Marco. Any idea when vsn 6 will be available?

--
Dean

 

[km]

I'm facing the SAME problem. i.e. Updating the clob column using the PL/SQL developer grid view, the new value recorded by my trigger for clob is always null.

Version details are :
Server : Oracle 10.2.0.3
Client : PL/SQL Developer 7.1.3.1381
OCI 9.2

Is this issue fixed in the above version of PL/SQL Developer ?

 

[Marco Kalter]

No, the LOB data is still written after initially writing an empty_clob().

 

[km]

Thanks a million for your prompt response, Marco.

Do you know whether there is a plan to overcome this ( by using temp lobs ) in the future releases of PL/SQL developer?