SQL loader not loading all columns in some environments

Question

I am facing a very weird problem, and the problem is related to SQL Loader. It loads all columns in some databases, while it skips the last column in other databases. And yes, the target table structure is identical among all databases.

Here is how csv file file looks like:

OPTIONS (ERRORS=50, DIRECT=TRUE, SKIP=1)
LOAD DATA
INFILE *
REPLACE
INTO TABLE STAGING.PRODUCTS
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS (
"PRODUCT_ID",
"PRODUCT_NAME",
"CLIENT_TECHNOLOGY")
BEGINDATA

1;Product1;N/A
2;Product2;N/A
....
100;Product100;N/A

and this is the log file, and this is from dev01. This is what I expect, aka all data loaded successfully:

SQL*Loader: Release 11.2.0.3.0 - Production on Thu Dec 8 15:29:21 2016

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

Control File:   products.csv
Data File:      products.csv
  Bad File:     products.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table STAGING.PRODUCTS, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
Message 3053 not found;  product=RDBMS; facility=UL
Message 3054 not found;  product=RDBMS; facility=UL
"PRODUCT_ID"                        FIRST     *   ;       CHARACTER            
"PRODUCT_NAME"                       NEXT     *   ;       CHARACTER            
"CLIENT_TECHNOLOGY"                  NEXT     *   ;       CHARACTER            


Table STAGING.PRODUCTS:
  100 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

On the other hand, say dev12 has different log. The last column is totally skipped the town.

SQL*Loader: Release 11.2.0.3.0 - Production on Thu Dec 8 15:29:21 2016

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

Control File:   products.csv
Data File:      products.csv
  Bad File:     products.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table STAGING.PRODUCTS, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
Message 3053 not found;  product=RDBMS; facility=UL
Message 3054 not found;  product=RDBMS; facility=UL
"PRODUCT_ID"                        FIRST     *   ;       CHARACTER            
"PRODUCT_NAME"                       NEXT     *   ;       CHARACTER          


Table STAGING.PRODUCTS:
  100 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Any idea what can be the reason behind it? The target table is the same in all environments.

create table STAGING.PRODUCTS
(
  product_id               VARCHAR2(64) not null,
  product_name             VARCHAR2(64) not null,
  client_technology        VARCHAR2(64) 
)

Thanks in advance :-)

Gary_W · Accepted Answer · 2016-12-08 15:14:57Z

1

You may have bigger issues at play. Notice these lines in both log files:

Message 3053 not found;  product=RDBMS; facility=UL
Message 3054 not found;  product=RDBMS; facility=UL

They should not be there. According to a little searching and this post, a file is missing and perhaps a client reinstall is in order? http://www.orafaq.com/forum/t/51572/2

Also you don't need double-quotes around the column names in the control file. I wonder if the double-quotes imply case-sensitive column names like when in SQL and perhaps the column name in dev12 doesn't match? Just a guess.

answered Dec 8, 2016 at 15:14

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jaanna Over a year ago

Thanks. Yes, re installation seems to have cured the problem.

Collectives™ on Stack Overflow

SQL loader not loading all columns in some environments

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related