Clob, toad, and a trigger not working

From: Shaw John-P55297 <P55297_at_motorola.com>
Date: Thu, 25 Apr 2002 07:58:31 -0800
Message-ID: <F001.0044F65E.20020425075831@fatcity.com>

I've encountered a weird error (no it's not my boss). It's on W2K and happens on 8.1.6 an 9i.

I've got two tables with clobs
CREATE TABLE CLOB_TEST (
  EID NUMBER,
  CLOB_TEST CLOB ) ;

---

CREATE TABLE CLOB_TEST_AFTER (
  EID NUMBER,
  CLOB_TEST CLOB ) ;
and created the following trigger:

CREATE OR REPLACE TRIGGER CLOB_TEST_AI AFTER INSERT ON CLOB_TEST FOR EACH ROW
DECLARE

	   buffer   long;
	   len      number(10);
	   eid      number;
	   clobber  clob;
	   sql_stmt varchar2(100);

BEGIN
 eid := :NEW.EID;
 clobber := :NEW.CLOB_TEST;
 len := dbms_lob.getlength(clobber);
 DBMS_LOB.READ(clobber,len,1,buffer);
 insert into clob_test_after (eid, clob_test) values (eid, buffer); END; This trigger works fine when I do an insert into the first table from sqlplus
but when I do an insert from TOAD or another third party application I get the following error messages:

ora-21560 argument 2 is null, invalid, or out of range
ora-06512 at sys.dbms_lob line 648
ora-06512 at clob_test_ai line 17
ora-04088 error during execution of trigger

clob_test_ai

Anyone know why this error occurs from third party stuff and not from sqlplus - or better yet a work around?
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Shaw John-P55297
  INET: P55297_at_motorola.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Apr 25 2002 - 10:58:31 CDT