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
