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

From: barry <bbulsara23_at_hotmail.com>
Date: 21 Nov 2003 09:02:02 -0800
Message-ID: <747f1dec.0311210902.1b53ecc2@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 - 11:02:02 CST