Direct Path Load Silently Inserts Strings That Exceed Column Length (Doc ID 1277810.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 to 11.2.0.2 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

You started SQL*Loader in direct path mode into a multibyte database using character length semantics and observed that some of the inserted records exceed the defined column length. The following example demonstrates this:

-- create the environment (in database on multibyte character set, like AL32UTF8/UTF8)
create user test identified by test default tablespace users temporary tablespace temp;
grant connect, resource to test;

connect test/test

create table a_tab
(
   len  number,
   text varchar2(10 char)
);

-- SQL*Loader control file ldr.ctl
options (direct=true)
load data
characterset utf8
infile 'ldr.dat'
truncate
into table a_tab
fields terminated by ';'
trailing nullcols
(
   len,
   text "ltrim (:text)"
)

-- input file ldr.dat
8;ABCDEFGH
20;12345678901234567890

Start SQL*Loader with:

#> sqlldr test/test control=ldr.ctl

This returns (in log file):

Table A_TAB:
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Verify the table in SQL*Plus:

SQL> select len, text, length (text), dump (text, 16) from a_tab;

LEN        TEXT       LENGTH(TEXT)
---------- ---------- ------------
DUMP(TEXT,16)
--------------------------------------------------------------------------------
         8 ABCDEFGH              8
Typ=1 Len=8: 41,42,43,44,45,46,47,48

        20 1234567890           20
Typ=1 Len=20: 31,32,33,34,35,36,37,38,39,30,31,32,33,34,35,36,37,38,39,30

=> The column TEXT contains 20 characters despite definition VARCHAR2(10 CHAR).

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms