SQLLDR Import of Flat files. How to handle Null fields properly
Date: Mon, 20 May 2002 16:24:50 +0100
Message-ID: <1021908247.8432.0.nnrp-01.d4f0f429_at_news.demon.co.uk>
Dear Group
1st of all apologies for annoying you gurus as I am sure you have plenty to do out there in the world than answer my questions.
I am relatively new to dba type stuff but am gradually getting more and more involved as time goes on. Currenly I am trying to import a flat file into Oracle. I seem to be having problems dealing with NULL or non-existant values in the data file.
Here is a the
command line and
control file and
a sample of the log file.
***Command Line***
sqlldr sa/scotland_at_report control=site_list.ctl
SQL*Loader: Release 8.1.6.0.0 - Production on Mon May 20 15:39:27 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
SQL*Loader-403: Referenced column IS_NULL not present in table TABLE_IMP_TEST. sqlldr sa/scotland_at_report control=site_list.ctl
SQL*Loader: Release 8.1.6.0.0 - Production on Mon May 20 15:40:51 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 50 Commit point reached - logical record count 101 Commit point reached - logical record count 154 Commit point reached - logical record count 202 Commit point reached - logical record count 255
sqlldr sa/scotland_at_report control=site_list.ctl
***Control File***
- Written BY TRees to import Flat file information
--
LOAD DATA
[Quoted] INFILE site_list3.txt
into TABLE TABLE_IMP_TEST
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(OBJID INTEGER,
ORACLE_REF CHAR NULLIF ORACLE_REF = BLANKS, ACCOUNT_NAME CHAR NULLIF ACCOUNT_NAME=BLANKS, ORG_ID CHAR NULLIF ORG_ID=BLANKS, SITE_ID INTEGER NULLIF SITE_ID=BLANKS, SITE_NAME CHAR NULLIF SITE_NAME=BLANKS, SHIP_REF CHAR NULLIF SHIP_REF=BLANKS, BILL_REF CHAR)
SQL*Loader: Release 8.1.6.0.0 - Production on Mon May 20 15:40:51 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Control File: site_list.ctl Data File: site_list3.txt Bad File: site_list3.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65536 bytes Continuation: none specified Path used: Conventional
Table TABLE_IMP_TEST, loaded from every logical record. Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ------------------
---
OBJID FIRST 4 INTEGER
ORACLE_REF NEXT * , O(") CHARACTER
NULL if ORACLE_REF = BLANKS
ACCOUNT_NAME NEXT * , O(") CHARACTER
NULL if ACCOUNT_NAME = BLANKS
ORG_ID NEXT * , O(") CHARACTER
NULL if ORG_ID = BLANKS
SITE_ID NEXT 4 INTEGER
NULL if SITE_ID = BLANKS
SITE_NAME NEXT * , O(") CHARACTER
NULL if SITE_NAME = BLANKS
SHIP_REF NEXT * , O(") CHARACTER
NULL if SHIP_REF = BLANKS
BILL_REF NEXT * , O(") CHARACTER
Record 11: Rejected - Error on table TABLE_IMP_TEST, column BILL_REF. Column not found before end of logical record (use TRAILING NULLCOLS) Record 13: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 14: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 20: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 24: Rejected - Error on table TABLE_IMP_TEST, column BILL_REF. Column not found before end of logical record (use TRAILING NULLCOLS) Record 29: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 44: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 45: Rejected - Error on table TABLE_IMP_TEST, column BILL_REF. Column not found before end of logical record (use TRAILING NULLCOLS) Record 51: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 58: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 82: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 83: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 84: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 85: Rejected - Error on table TABLE_IMP_TEST, column BILL_REF. Column not found before end of logical record (use TRAILING NULLCOLS) Record 89: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 97: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 100: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 103: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 104: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 105: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 111: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 125: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 126: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 130: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 135: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 141: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 148: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 150: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 167: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 168: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 175: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 186: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 190: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 191: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 212: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 213: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 214: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 228: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 229: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 230: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 231: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 237: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 239: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 240: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 243: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 256: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 257: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 258: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 259: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 260: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field Record 261: Rejected - Error on table TABLE_IMP_TEST, column SITE_NAME. no terminator found after TERMINATED and ENCLOSED field
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table TABLE_IMP_TEST:
210 Rows successfully loaded.
51 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.
Space allocated for bind array: 65352 bytes(42 rows) Space allocated for memory besides bind array: 0 bytes Total logical records skipped: 0 Total logical records read: 261 Total logical records rejected: 51 Total logical records discarded: 0
Run began on Mon May 20 15:40:51 2002
Run ended on Mon May 20 15:40:52 2002
Elapsed time was: 00:00:00.82 CPU time was: 00:00:00.14
***Here is a sample of the bad values :-***
11,,"AAPT","AAPT","3038","AAPT",,
13,"2022","ABB Network Partner AG","ABB1","779","ABB Network Partner
AG","CS-ABB1-779/1","CS-ABB1-779/1"
14,"2022","ABB Network Partner AG","ABB1","872","Wassmer &
Zurcher","5665","5665"
20,"1495","ABB Twizel","ABB2","836","ABB Twizel","CS-ABB2-836","CS-ABB2-836" 24,,"ACT","ACT2","2472","ACT Manufacturing (Thailand) PCL",, 29,"1503","AEG Belgium","AEG1","781","AEG Belgium","4492","CS-AEG1-781" 44,"2136","ALLTELL","ALT","929","AllTel","4139","4139" 45,,"ALMACEN SERVICIO DE REPARACIONES","ALM","1003","ALMACEN SERVICIO DEREPARACIONES",,
51,"1945","ARIZONA TELEPHONE","ART","999","ARIZONA TELEPHONE","4141","4141" 58,"2172","AT&T","ATT1","964","AT&T","4138","5121" 82,"2176","AT&T Wireless","ATT","791","AT&T","CS-DUMMY1-791","CS-DUMMY1-791" 83,"2176","AT&T Wireless","ATT","898","AT&T","CS-DUMMY1-898","CS-DUMMY1-898" 84,"2176","AT&T Wireless","ATT","912","AT&T","4138","4138" 85,,"ATCOM","ATCOM","2910","ATCOM ApS",, 89,"2023","Abiatar S.A. (Movicom Uruguay)","ABI","792","Abiatar S.A.
(Movicom Uruguay)","3947","3947"
97,"1506","Aerolite Somalia Telecom","AST","910","Aerolite Somalia
Telecom","CS-DUMMY1-910","CS-DUMMY1-910" 100,"2026","Air Lanka","AIR","903","Air Lanka","CS-DUMMY1-903","CS-DUMMY1-903" 103,"2028","Airways Corporation","AIR1","837","Airways Corporation","CS-AIR1-837/1","CS-AIR-837" 104,"2028","Airways Corporation","AIR1","838","Airways Corporation","CS-AIR1-838","CS-AIR-837" 105,"2028","Airways Corporation","AIR1","839","AirwaysCorporation","CS-AIR1-839","CS-AIR-837"
111,"1477","Alcatel CIT","ALC1","782","Alcatel CIT","CS-ALC1-782","CS-ALC1-782"
125,"1517","Anditel LTDA","AND2","794","Anditel","CS-AND2-794","CS-AND2-794" 126,"1518","Andrew Australia","AND1","780","Andrew Australia","CS-AND1-780","CS-AND1-780" 130,"1522","Aplitec","APL1","888","Aplitec","CS-APL1-888","CS-APL1-888" 135,"1524","Aramco Services Co","ASC","970","Aramco Services Company","1824","1824" 141,"1527","Ascom Radiocom Limited","ASC1","796","Ascom Radiocom Limited","CS-ASC1-796","CS-ASC1-796" 148,"1532","Asian High Technology Corp","ASI1","795","Asian HIgh TechnologyCorp","CS-ASI1-795","CS-ASI1-795"
150,"1533","Associated Communication Technology LTd","ACT","732","Associated Communication Technology LTd","5690","5690" 167,"2030","B.M.P. Ingenieros","BMP","942","B.M.P. Ingenieros S.A., C/O: KY International, Inc.","6123","6123"
168,"2030","B.M.P. Ingenieros","BMP","943","B.M.P. Ingenieros S.A., C/O: KY International, Inc.","5245",
175,"1554","BC TEL MOBILITY CELLULAR","BTM","712","BC TEL MOBILITY CELLULAR","4201","CS-DMC-184"
186,"2518","BHP PETROLEUM","BHP","992","BHP PETROLEUM","3541","4486" 190,"1563","BOARD OF PUBLIC UTILITIES","BPU","713","BOARD OF PUBLIC UTILITIES","3360-SV1","3360-SV1" 191,"1564","BORDERCOMM, INC.","BOR","948","BORDERCOMM, INC.",,"3224" 212,"2189","Baja Cellular","BAJ","714","BAJACELLULAR","CS-BAJ-803","CS-BAJ-803"
213,"2189","Baja Cellular","BAJ","802","BAJA CELLULAR","1271","1271" 214,"2189","Baja Cellular","BAJ","803","Baja Celular Mexicana SA de CV","CS-BAJ-803/1","5041" 228,"2190","Belam Inc","BEL","657","Belam Kaunas","2685","2685" 229,"2190","Belam Inc","BEL","891","BelamTelekomunikacijos","CS-DUMMY-891","CS-DUMMY-891"
230,"2190","Belam Inc","BEL","899","Belam Telekomunikacijos","CS-DUMMY1-899","CS-DUMMY1-899" 231,"2190","Belam Inc","BEL","935","Belam Telekomunikacijos","3929","3929"237,"2191","Bell Mobility","BELL","711","Bell Mobility","4178","CS-BELL-59" 239,"2053","Berlin International Inc","BER1","805","Berlin International Inc","CS-BER1-805","CS-BER1-805"
240,"2053","Berlin International Inc","BER1","806","Berlin International Inc","CS-BER1-806","CS-BER1-806"
243,"1561","Bi-National Communications Sys","BCS","894","BINATIONAL COMMUNICATIONS","CS-BCS-60","CS-BCS-60"
256,"2201","British Telecom","BT","100","British Telecom Plc","CS-BT-100","CS-BT-100" 257,"2201","British Telecom","BT","101","British Telecom Plc","CS-BT-101","CS-BT-101" 258,"2201","British Telecom","BT","102","British Telecom Plc","CS-BT-102","CS-BT-102" 259,"2201","British Telecom","BT","103","British Telecom Plc","CS-BT-103","CS-BT-103" 260,"2201","British Telecom","BT","104","British Telecom Plc","CS-BT-104","CS-BT-104" 261,"2201","British Telecom","BT","105","British Telecom Plc","CS-BT-105","CS-BT-105"Received on Mon May 20 2002 - 17:24:50 CEST