Toad World® Forums

Trigger on Table with CLOB Raises Exception upon Toad's Edit Feature

Hello!

I have a table with a CLOB field and a trigger that manipulates the CLOB data. Insert, update, etc work fine in SQL, but the trigger raises an exception when I edit via Toad’s EDIT command (also Toad’s Database -> Import -> Import Table Data feature).

Steps to reproduce:

  • Build example table as shown below
  • Build trigger as shown below
  • Test success case: INSERT INTO lob_trigger_test VALUES (‘x’)
  • Invoke Toad’s EDIT feature by typing: EDIT lob_trigger_test
  • In the data grid, click the “+” icon (add a row); enter any value; then click the check-mark icon (post the added row)
  • This results in the popup error shown in the trigger code below
    This strikes me as a bug in Toad. Do you agree?

Thank you for your insights … Grant

Table code:

CREATE TABLE lob_trigger_test (query_text CLOB)

Trigger code:

CREATE OR REPLACE TRIGGER tr_lob_trigger_test
BEFORE INSERT OR UPDATE ON lob_trigger_test
FOR EACH ROW
DECLARE
l_query_sql CLOB := :new.query_text;
BEGIN
/*
When we use Toad’s EDIT command, this dbms_lob.append command raises
this error:
ORA-22275: invalid LOB locator specified
ORA-06512: at “SYS.DBMS_LOB”, line 639
ORA-06512: at “Z_GSTEVENS.TR_LOB_TRIGGER_TEST”, line 14
ORA-04088: error during execution of trigger ‘Z_GSTEVENS.TR_LOB_TRIGGER_TEST’
Without the append, the error is worse; I want to EXECUTE IMMEDITE the
CLOB value; but instead of an error popup, it disconnects my Toad session.
*/
dbms_lob.append (l_query_sql, ’ ');

– EXECUTE IMMEDIATE l_query_sql;
END;

I can’t reproduce the problem in the Editor with the EDIT command. What version of Toad are you using?

I CAN reproduce it with the Insert Table Data window. The reason is that it inserts CLOBS with a RETURNING clause as below - which causes Oracle to throw the error for the trigger.

Insert into LOB_TRIGGER_TEST (
QUERY_TEXT)
Values (
Empty_Clob())
returning QUERY_TEXT into :QUERY_TEXT

There was a reason why I wrote it this way. Let me see if there is anything I can do to make a “normal” insert statement work without breaking anything else. I’ll get back to you on that.

OK, next beta - Import Table Data with CLOBs should no longer cause problems with your trigger.

Wonderful - thank you, John!

To the “version” question: Toad for Oracle 12.5.1.1, Base Edition; Add-Ons:

Yup, that explains it. The problem with EDIT and triggers on CLOBs was fixed in 12.8.