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
CREATE TABLE lob_trigger_test (query_text CLOB)
CREATE OR REPLACE TRIGGER tr_lob_trigger_test
BEFORE INSERT OR UPDATE ON lob_trigger_test
FOR EACH ROW
l_query_sql CLOB := :new.query_text;
When we use Toad’s EDIT command, this dbms_lob.append command raises
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;