Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> CLOB trigger problem

CLOB trigger problem

From: Campbell, James <>
Date: Tue, 18 Sep 2001 22:16:28 -0700
Message-ID: <>

Dear listers,

I am trying to copy an updated CLOB from a table in one database (let's call it A) to the equivalent table in another database (called B just to be original). I have written triggers and procedures to do this (one package on each database), and I am passing the contents of the CLOB to the remote update procedure as VARCHAR fields in a PL/SQL table.

Everything works perfectly well when I run an update against table A. I am connecting via TOAD and am logged on to Oracle as the schema owner. The contents of the CLOB are retrieved, passed across and used to update the remote table.

When the client updates the CLOB in table A, however, the select statement in my package on database A which 'locates' the CLOB, retrieves a CLOB of zero length. No exception occurs in the Select statement. The client's update succeeds on table A, and my package sends an empty PL/SQL table to the remote procedure (and the CLOB on the remote table is duly erased). The client is connecting through an ASP-driven web interface via IIS, which as far as I can tell is also logging on to Oracle as the schema owner.

Anyone encountered this sort of behaviour before? I've just about run out of ideas. If I can't solve this by direct means I can probably do something kludgy like launch the remote update as a background process via DBMS_JOB.SUBMIT, but I'd far rather have it under transactional control.

Oracle version is on both databases.

Can supply contents of triggers etc. if needed.

James Campbell

Please see the official ORACLE-L FAQ:

Author: Campbell, James

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: (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 Wed Sep 19 2001 - 00:16:28 CDT

Original text of this message