我面临一个非常奇怪的问题,这个问题与SQL加载器有关。它加载某些数据库中的所有列,同时跳过其他数据库中的最后一列。是的,目标表结构在所有数据库中都是相同的。
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
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.
另一方面,假设dev12具有不同的日志。最后一列完全跳过了城镇。
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.
你知道这背后的原因吗?目标表在所有环境中都是相同的。
create table STAGING.PRODUCTS
(
product_id VARCHAR2(64) not null,
product_name VARCHAR2(64) not null,
client_technology VARCHAR2(64)
)
