CODEWITHSUNDEEP
oracle-databasesql-loader
I_am_groot
I_am_groot

Reputation: 49

How to ignore varying number of extra column Field in data file when loading through sqlldr

I have data file of 10 pipe separated data. Data file may have less than 10 fields or more than 10 fields. How should I write control file in this case? I want only 10 fields in my table. If there are less fields those should be filled as null and if more those should be ignored.

Upvotes: 1

Views: 881

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

I'd say that it is TRAILING NULLCOLS you're looking for.

By the way, as there's varying number of fields in input file, how are you loading different datatypes? I presumed they are all VARCHAR2s.

Here's an example (I'm using only 4 columns; didn't feel like typing that much).

Table description:

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               VARCHAR2(10)
 COL2                                               VARCHAR2(10)
 COL3                                               VARCHAR2(10)
 COL4                                               VARCHAR2(10)

Control file:

load data 
infile *
replace
into table test
fields terminated by "|" 
trailing nullcols
(
col1,
col2,
col3,
col4
)

begindata
123|Little|foot|four
456|Access||
789|two|three|four|five|six|seven
111|

Loading session & its results:

SQL> $sqlldr scott/tiger@kc11g control=test31.ctl log=test31.log

SQL*Loader: Release 11.2.0.1.0 - Production on Uto Lis 22 08:02:23 2019

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;

COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
123        Little     foot       four
456        Access
789        two        three      four
111

SQL>

Upvotes: 2

Related Questions