SQL*Loader Express bug – not!

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

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: TEST
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table TEST:
  3 Rows successfully loaded.

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply