Good explanation.
"VC" <boston103_at_hotmail.com> wrote in message
news:ATzvb.72225$Dw6.365809_at_attbi_s02...
> 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 Sat Nov 22 2003 - 01:21:55 CST
