My Oracle Support Banner

SQL*Loader - Trigger NEW Value For LOB's Remains Empty After INSERT With sqlldr (Doc ID 1942031.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 7.3.4.0 to 12.1.0.2 [Release 7.3.4 to 12.1]
Oracle Database - Personal Edition - Version 7.3.4.0 to 12.1.0.2 [Release 7.3.4 to 12.1]
Oracle Database - Standard Edition - Version 7.3.4.0 to 12.1.0.2 [Release 7.3.4 to 12.1]
Information in this document applies to any platform.

Symptoms

1. You have created a test user, e.g.:

connect / as sysdba

create user tc identified by tc default tablespace users temporary tablespace temp;
alter user tc quota unlimited on users;
grant create session,create table, create trigger to tc;


2. You also created a trigger to audit the load of data into a table, e.g.:

connect tc/tc

create table testload (id number, note clob default empty_clob());
create table testaudit (id number, note clob default empty_clob());
create or replace trigger my_trig before insert on testload
  for each row
declare
  v_desc  clob;
begin
  v_desc := :new.note;
  insert into testaudit values (:new.id, v_desc);
end my_trig;
/


3. You test an insert in SQL*Plus, and the audit table is successfully populated, e.g.:

insert into tc.testload values (1,'Line 1');
commit;

col note for a10
select * from tc.testload;

         ID NOTE
------------ ----------
          1 Line 1

select * from tc.testaudit;

         ID NOTE
------------ ----------
          1 Line 1

3. When you test with SQL*Loader, the audit table contains an EMPTY_CLOB instead of the LOB value, e.g.:

File testload.dat
-----------------
2;Line 2
3;Line 3

File testload.ctl
-----------------
LOAD DATA
INFILE testload.dat
APPEND INTO TABLE tc.testload
FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
 ID INTEGER EXTERNAL NULLIF ID=BLANKS,
 NOTE CHAR(8000) NULLIF NOTE=BLANKS
)

$ sqlldr tc/tc control=testload.ctl


4. It turns out that the new value of the LOB column in the trigger remains empty.

select * from tc.testload;

         ID NOTE
------------ ----------
          1 Line 1
          2 Line 2
          3 Line 3

select * from tc.testaudit;

         ID NOTE
------------ ----------
          1 Line 1
          2
          3


select id from tc.testaudit where length(note) = 0;

          ID
------------
           2
           3

In the audit table, the LOB columns have an empty_clob when the data is loaded with SQL*Loader.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.