I’m still studying for my Oracle 12c OCP exam and I was trying to run a simple example of using SQL*Loader Express and the first thing I did blew up and I think it is a bug.  When I load a table with one or two columns it works fine, but when I load a table with 3 or 4 columns the last column is not loaded.  Tell me this isn’t a special feature! 🙂

First I create the table with four columns:

create table test
(a varchar2(20),
b varchar2(20),
c varchar2(20),
d varchar2(20));

Then I create a comma separated values file named test.dat with four values per line:

[oracle@ora12c dpsl]$cat test.dat a,b,c,d f,g,h,i j,k,l,m Then I run sql*loader in express mode: [oracle@ora12c dpsl]$ sqlldr system/xxxxxx table=test

SQL*Loader: Release 12.1.0.1.0 - Production on Mon Apr 21 07:32:43 2014

Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table TEST:

Check the log files:
test.log
test_%p.log_xt
for more information about the load.

Then I query the newly loaded table:

ORCL:CDB$ROOT:SYSTEM>select * from test; A B D -------------------- -------------------- -------------------- a b d f g i j k m Queue the mysterious music. Actually, now that I look at it really it is the third column that is missing. Maybe it doesn’t work with a column named C. Sure enough, here it is with column C replaced with column X: A B X D -------------------- -------------------- -------------------- -------------------- a b c d f g h i j k l m So, I guess SQL*Loader Express doesn’t work with columns named C? Odd. – Bobby Update on 05/16/2014: As you probably can expect, this was user error on my part. My standard header for sqlplus scripts has this code: column u new_value us noprint; column n new_value ns noprint; column c new_value cs noprint; select name n from v$database;
select user u from dual;
SELECT SYS_CONTEXT('USERENV', 'CON_NAME') c FROM DUAL;

I use this code to build a prompt that will tell me which container I’m in like this:

set sqlprompt &ns:&cs:&us>

But, this means I can’t use columns named n, u, or c, but in my sql*loader test I was using c.  So, not a bug, just a user error!

– Bobby