CODEWITHSUNDEEP
oracle-databasesql-loader
cluhendrix
cluhendrix

Reputation: 3

sqlldr only loading first character of string into column

I am trying to load data into a database table using sqlldr. The issue I'm encountering is that only the first character of each line is being loaded. I am using Oracle version 12.1 on AIX. My data is a single column text file. Each record is of variable length. In this test set we limit the data to 10 characters. Each row is on a new line. It should be loaded into a single column table.

My empty table is defined as:

create table test_table (test_num varchar2(10));

My data looks like but without the doublespacing:

3675536758

8370530737

9318078

5395653669

Looking at the data in od -c it looks like:

0000000    3   6   7   5   5   3   6   7   5   8  \n   8   3   7   0   5
0000020    3   0   7   3   7  \n   9   3   1   8   0   7   8  \n   5   3
0000040    9   5   6   5   3   6   6   9  \n
0000051

My control file looks like:

load data
 infile 'test.txt' "str '\n'"
 into TABLE test_table
 (test_num char "trim(:test_num)")

The issue I have is only the the first character each line is being loaded into the table.

This behavior persists with or without the trim function and with or without the "str '\n'"

Any suggestions would be appreciated.

Upvotes: 0

Views: 3817

Answers (2)

Littlefoot
Littlefoot

Reputation: 142713

Alternatively, terminated by whitespace might help:

load data
infile * 
replace
into table test_table
(test_num  terminated by whitespace
)
begindata
3675536758
8370530737
9318078
5395653669

Loading session & the result:

SQL> $sqlldr scott/tiger@orclcontrol=test23.ctl

SQL*Loader: Release 11.2.0.2.0 - Production on Pet Vel 23 07:45:32 2018

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

Commit point reached - logical record count 3
Commit point reached - logical record count 4

SQL> select * from test_table;

TEST_NUM
----------
3675536758
8370530737
9318078
5395653669

SQL>

Upvotes: 1

Patrick Bacon
Patrick Bacon

Reputation: 4640

Modify (test_num char "trim(:test_num)") to (test_num char(10) "trim(:test_num)") and the records will be loaded as expected.

char would be one character whereas char(10) would be 10 characters.

On a windows server, I changed the control file to not use the stream record format (not the source of the problem).

load data
 infile * 
 into TABLE test_table
 (test_num char(10) "trim(:test_num)")
BEGINDATA
3675536758
8370530737
9318078
5395653669

enter image description here

We see these records now:

SCOTT@erptst>select * from test_table;
TEST_NUM
3675536758
8370530737
9318078
5395653669

Upvotes: 4

Related Questions