Re: Bug? Determining lengths of CLOB's in trigger/Oracle 8.1.7

Hello barry,

The phenomenon you are observing is not a bug but Oracle's expected behaviour.

1. The LOB in Oracle can be stored either inline (in row) if it does not exceed 4000 bytes, or in a separate lob segment.
2. When you used an 'insert' to load the lob, an inline insert happened and the trigger fired with the correct lob length.
3. When you loaded the lob using 'sqlldr', the sqlldr created a lob locator pointing to an empty lob segment and the length obtained in the trigger was zero. Then sqlldr wrote data (using dbms_lob.write) into the lob segment which did not cause the trigger to fire because the row in the table was _not_ changed. Here's an excerpt from the Oracle manual:

 "Note also that because LOB data is loaded after the array insert has been performed, BEFORE and AFTER row triggers may not work as expected for LOB columns. This is because the triggers fire before SQL*Loader has a chance to load the LOB contents into the column."

Rgds.

"barry" <bbulsara23_at_hotmail.com> wrote in message news:747f1dec.0311210902.1b53ecc2_at_posting.google.com...
> After spending another day tracking down another simple problem, I
> have created the following contrived scenario and am suggesting that
> there is an Oracle bug somewhere, and I don't know where. I'd
> appreciate someone shining a light here if at all possible, or
> confirmation of the bug.
>
> Here's the code/script I used to highlight the problem.
>
>
> SQL>
> SQL> CREATE TABLE tblTestCLOB(testCLOB CLOB);
>
> Table created.
>
> SQL> CREATE TABLE tblTestSIZE(clobsize NUMBER);
>
> Table created.
>
> SQL>
> SQL> CREATE OR REPLACE TRIGGER trg_Test_ins
> 2 AFTER INSERT ON tblTestCLOB
> 3 FOR EACH ROW
> 4 DECLARE
> 5 BEGIN
> 6 INSERT INTO tblTestSIZE(clobsize)
> 7 VALUES (DBMS_LOB.GETLENGTH(:NEW.testCLOB));
> 8 END;
> 9 /
>
> Trigger created.
>
> SQL>
> SQL> INSERT INTO tblTestCLOB(testCLOB)
> 2 VALUES ('Freddy');
>
> 1 row created.
>
> SQL>
> SQL> SELECT clobsize
> 2 FROM tblTestSIZE;
>
> CLOBSIZE
> ----------
> 6
>
> SQL>
> SQL>
>
> This is the behaviour I expect - table tblTestSIZE is populated with a
> row containing the value 6 (the length of 'Freddy') and this value is
> inserted by the trigger trg_Test_ins.
>
>
> However, if I attempt to populate the table using SQLLDR, rather than
> the SQL INSERTS above, using the following control file:
>
> LOAD DATA
> INFILE *
> INTO TABLE tblTestCLOB
> REPLACE
> FIELDS TERMINATED BY ','
> ( testCLOB )
> BEGINDATA
> Freddy
> Brian
> Roger
> John
>
>
> and then query the tables, I see the following:
>
>
> SQL>
> SQL> select * from tblTestCLOB;
>
> TESTCLOB
> ----------------------------------
> Freddy
> Brian
> Roger
> John
>
> SQL>
> SQL>
> SQL> SELECT clobsize
> 2 FROM tblTestSIZE;
>
> CLOBSIZE
> ----------
> 0
> 0
> 0
> 0
>
>
> Why is the CLOBSIZE 0 ? I think it should be 6, 5, 5, and 4 being the
> lengths of "Freddy", "Brian", "Roger", and "John" - just as I have
> proven for "Freddy" above when I insert "Freddy" with an SQL INSERT
> statement.
>
> My initial feeling was that SQLLDR was initially creating an
> EMPTY_CLOB() upon the insert, then UPDATING it afterwards (and as my
> trigger was determining the CLOB length on the initial insert). But if
> I change my trigger to fire on an UPDATE rather than an INSERT, it
> doesn't fire at all.
>
> Thank you for all contributing/thinking about my dilemma.
> Barry
>
>
> And finally - the necesary version details.
>
>
> SQL>
> SQL> SELECT * FROM V$VERSION;
>
> BANNER
> ----------------------------------------------------------------
> Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> PL/SQL Release 8.1.7.0.0 - Production
> CORE 8.1.7.0.0 Production
> TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
> NLSRTL Version 3.4.1.0.0 - Production
>
> SQL>
>
>
> C:\test>SQLLDR
>
> SQL*Loader: Release 9.2.0.1.0 - Production on Fri Nov 21 16:49:26 2003
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>
>
> C:\test>
> C:\test>ver
>
> Microsoft Windows 2000 [Version 5.00.2195]
>
> C:\test>
> C:\test>
Received on Fri Nov 21 2003 - 20:30:56 CST