Home » RDBMS Server » Server Utilities » ?Trivial? sqlldr problem (11.2.0.1.0. client 11.1.0.7.0 server windows)
| ?Trivial? sqlldr problem [message #490846] |
Thu, 27 January 2011 12:37  |
tmcallister
Messages: 107
Registered: December 2007
|
Senior Member |
|
|
Can anyone see why the following does not work? I've tried everything and I'm certain I'm missing something trivial.
It load the appropriate rows into the first INTO TABLE ... WHEN; but discards the rest.
This behavior persists even when the INTO TABLE ... WHEN clauses are rearranged...
CREATE TABLE AG_HDR
(
AG VARCHAR2 (1024),
AGREEMENT_ID VARCHAR2 (1024),
RECORD_TYPE VARCHAR2 (1024),
AGREEMENT_TYPE_CODE VARCHAR2 (1024),
CODE_TABLE_NAME VARCHAR2 (1024),
AGREEMENT_STATUS_CODE VARCHAR2 (1024),
STATUS_CODE_TABLE VARCHAR2 (1024),
LAST_UPDATE_DATE VARCHAR2 (1024),
WORKFLOW_STREAM_CODE VARCHAR2 (1024),
WORKFLOW_CODE_TABLE VARCHAR2 (1024),
OFFSET_COMPENSATION_FLAG VARCHAR2 (1024),
RECORD_STATUS VARCHAR2 (1024)
);
CREATE TABLE AG_DREP
(
AG VARCHAR2 (1024),
AGREEMENT_ID VARCHAR2 (1024),
RECORD_TYPE VARCHAR2 (1024),
CLIENT_ID VARCHAR2 (1024),
ADDRESS_ID VARCHAR2 (1024)
);
CREATE TABLE AG_PAR
(
AG VARCHAR2 (1024),
AGREEMENT_ID VARCHAR2 (1024),
RECORD_TYPE VARCHAR2 (1024),
CLIENT_ID VARCHAR2 (1024),
ADDRESS_ID VARCHAR2 (1024),
PARTICIPATION_PERCENTAGE VARCHAR2 (1024)
);
LOAD DATA
INFILE *
TRUNCATE
INTO TABLE AG_HDR WHEN AG = 'AG' and RECORD_TYPE = 'HDR'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(AG,AGREEMENT_ID,RECORD_TYPE,AGREEMENT_TYPE_CODE,CODE_TABLE_NAME,AGREEMENT_STATUS_CODE,STATUS_CODE_TABLE,LAST_UPDATE_DATE,WORKFLOW_STREAM_CODE,WORKFLOW_CODE_TABLE,OFFSET_COMPENSATION_FLAG,RECORD_STATUS)
INTO TABLE AG_DREP WHEN AG = 'AG' and RECORD_TYPE = 'DREP'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(AG,AGREEMENT_ID,RECORD_TYPE,CLIENT_ID,ADDRESS_ID)
INTO TABLE AG_PAR WHEN AG = 'AG' and RECORD_TYPE = 'PAR'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(AG,AGREEMENT_ID,RECORD_TYPE,CLIENT_ID,ADDRESS_ID,PARTICIPATION_PERCENTAGE)
begindata
"AG","001 6","HDR","001","AGRTYPE","7","ENSTATS","2005/06/15 11:11:39","1972/06/01 00:00:00","","WORKFLOWSTREAM","N","A"
"AG","001 6","DREP","0000233","009"
"AG","001 6","PAR","0000233",100.0000000
"AG","001 6","LTRD","","N"
"AG","001 6","TERM","1962/06/01 00:00:00",10,0,0,"1972/06/01 00:00:00","8888/12/31 23:59:59","0","N","PRIMARY"
"AG","001 6","RENT","1989/06/01 00:00:00","",640.00,256.0000,256.0000,"Y"
"AG","001 6","LDAT"," ","","AGRLDCAT","","LDPURPOS"
"AG","001 6","AGLND","00","4-10-005:29","","RDALLOWTX","","","PMODTX","",256.0000
"AG","001 6","AGRGTS","00","N","03","SUBSTNCE","09","ZONEQUAL","0000","ZONECODE"," ","03","ZONEQUAL","2130","ZONECODE","D00008 "
"AG","001 6","WELLEVT","ABWI100132900510W400","0","WLDELMTR","02","WLFLUID","02","WLMODE","00","WLTYPE","00","WLSTRUCT"
"AG","001 6","AGCANC","01","AGRCANCL","1988/12/11 00:00:00"
"AG","001 6","RELAGR","001","AGRTYPE"," ","","AGRTYPE","","ENSTATS","1983/01/01 00:00:00","019","RELAMEND","0",0.0000,"","AGRCANCL",""
"AG","001 6","RELAGR","001","AGRTYPE","001 72969","001","AGRTYPE","","ENSTATS","1962/06/01 00:00:00","073","RELAMEND","0",0.0000,"","AGRCANCL",""
"AG","001 7","HDR","001","AGRTYPE","5","ENSTATS","2010/10/13 19:30:35","1972/06/01 00:00:00","","WORKFLOWSTREAM","N","A"
"AG","001 7","DREP","8083385","001"
"AG","001 7","PAR","8083385",20.0000000
"AG","001 7","PAR","1002769",80.0000000
"AG","001 7","LTRD","2010/10/12 00:00:00","N"
"AG","001 7","TERM","1962/06/01 00:00:00",10,0,0,"1972/06/01 00:00:00","","0","N","PRIMARY"
"AG","001 7","RENT","2011/06/01 00:00:00","",896.00,256.0000,256.0000,"Y"
This is my result:
SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jan 27 11:35:23 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: agreement.ctl
Data File: agreement.ctl
Bad File: agreement.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 256000 bytes
Continuation: none specified
Path used: Conventional
Table AG_HDR, loaded when AG = 0X4147(character 'AG')
and RECORD_TYPE = 0X484452(character 'HDR')
Insert option in effect for this table: TRUNCATE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
AG FIRST * , O(") CHARACTER
AGREEMENT_ID NEXT * , O(") CHARACTER
RECORD_TYPE NEXT * , O(") CHARACTER
AGREEMENT_TYPE_CODE NEXT * , O(") CHARACTER
CODE_TABLE_NAME NEXT * , O(") CHARACTER
AGREEMENT_STATUS_CODE NEXT * , O(") CHARACTER
STATUS_CODE_TABLE NEXT * , O(") CHARACTER
LAST_UPDATE_DATE NEXT * , O(") CHARACTER
WORKFLOW_STREAM_CODE NEXT * , O(") CHARACTER
WORKFLOW_CODE_TABLE NEXT * , O(") CHARACTER
OFFSET_COMPENSATION_FLAG NEXT * , O(") CHARACTER
RECORD_STATUS NEXT * , O(") CHARACTER
Table AG_DREP, loaded when AG = 0X4147(character 'AG')
and RECORD_TYPE = 0X44524550(character 'DREP')
Insert option in effect for this table: TRUNCATE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
AG NEXT * , O(") CHARACTER
AGREEMENT_ID NEXT * , O(") CHARACTER
RECORD_TYPE NEXT * , O(") CHARACTER
CLIENT_ID NEXT * , O(") CHARACTER
ADDRESS_ID NEXT * , O(") CHARACTER
Table AG_PAR, loaded when AG = 0X4147(character 'AG')
and RECORD_TYPE = 0X504152(character 'PAR')
Insert option in effect for this table: TRUNCATE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
AG NEXT * , O(") CHARACTER
AGREEMENT_ID NEXT * , O(") CHARACTER
RECORD_TYPE NEXT * , O(") CHARACTER
CLIENT_ID NEXT * , O(") CHARACTER
ADDRESS_ID NEXT * , O(") CHARACTER
PARTICIPATION_PERCENTAGE NEXT * , O(") CHARACTER
value used for ROWS parameter changed from 64 to 43
Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.
Record 6: Discarded - failed all WHEN clauses.
Record 7: Discarded - failed all WHEN clauses.
Record 8: Discarded - failed all WHEN clauses.
Record 9: Discarded - failed all WHEN clauses.
Record 10: Discarded - failed all WHEN clauses.
Record 11: Discarded - failed all WHEN clauses.
Record 12: Discarded - failed all WHEN clauses.
Record 13: Discarded - failed all WHEN clauses.
Record 15: Discarded - failed all WHEN clauses.
Record 16: Discarded - failed all WHEN clauses.
Record 17: Discarded - failed all WHEN clauses.
Record 18: Discarded - failed all WHEN clauses.
Record 19: Discarded - failed all WHEN clauses.
Record 20: Discarded - failed all WHEN clauses.
Table AG_HDR:
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
18 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table AG_DREP:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
20 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table AG_PAR:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
20 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 255162 bytes(43 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 20
Total logical records rejected: 0
Total logical records discarded: 18
Run began on Thu Jan 27 11:35:23 2011
Run ended on Thu Jan 27 11:35:23 2011
Elapsed time was: 00:00:00.42
CPU time was: 00:00:00.06
|
|
|
|
| Re: ?Trivial? sqlldr problem [message #490851 is a reply to message #490846] |
Thu, 27 January 2011 12:48  |
tmcallister
Messages: 107
Registered: December 2007
|
Senior Member |
|
|
./sigh
Messing around for 2 hours, then I post, then I find the solution in ~2 minutes. I *KNEW* it was something trivial.
Needed to add POSITION(1) to the subsequent INTO TABLE ... WHEN clauses.
i.e.
LOAD DATA
INFILE *
TRUNCATE
INTO TABLE AG_HDR WHEN AG = 'AG' and RECORD_TYPE = 'HDR'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(AG,AGREEMENT_ID,RECORD_TYPE,AGREEMENT_TYPE_CODE,CODE_TABLE_NAME,AGREEMENT_STATUS_CODE,STATUS_CODE_TABLE,LAST_UPDATE_DATE,WORKFLOW_STREAM_CODE,WORKFLOW_CODE_TABLE,OFFSET_COMPENSATION_FLAG,RECORD_STATUS)
INTO TABLE AG_DREP WHEN AG = 'AG' and RECORD_TYPE = 'DREP'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(AG POSITION(1),AGREEMENT_ID,RECORD_TYPE,CLIENT_ID,ADDRESS_ID)
INTO TABLE AG_PAR WHEN AG = 'AG' and RECORD_TYPE = 'PAR'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(AG POSITION(1),AGREEMENT_ID,RECORD_TYPE,CLIENT_ID,ADDRESS_ID,PARTICIPATION_PERCENTAGE)
begindata
"AG","001 6","HDR","001","AGRTYPE","7","ENSTATS","2005/06/15 11:11:39","1972/06/01 00:00:00","","WORKFLOWSTREAM","N","A"
"AG","001 6","DREP","0000233","009"
"AG","001 6","PAR","0000233",100.0000000
"AG","001 6","LTRD","","N"
"AG","001 6","TERM","1962/06/01 00:00:00",10,0,0,"1972/06/01 00:00:00","8888/12/31 23:59:59","0","N","PRIMARY"
"AG","001 6","RENT","1989/06/01 00:00:00","",640.00,256.0000,256.0000,"Y"
"AG","001 6","LDAT"," ","","AGRLDCAT","","LDPURPOS"
"AG","001 6","AGLND","00","4-10-005:29","","RDALLOWTX","","","PMODTX","",256.0000
"AG","001 6","AGRGTS","00","N","03","SUBSTNCE","09","ZONEQUAL","0000","ZONECODE"," ","03","ZONEQUAL","2130","ZONECODE","D00008 "
"AG","001 6","WELLEVT","ABWI100132900510W400","0","WLDELMTR","02","WLFLUID","02","WLMODE","00","WLTYPE","00","WLSTRUCT"
"AG","001 6","AGCANC","01","AGRCANCL","1988/12/11 00:00:00"
"AG","001 6","RELAGR","001","AGRTYPE"," ","","AGRTYPE","","ENSTATS","1983/01/01 00:00:00","019","RELAMEND","0",0.0000,"","AGRCANCL",""
"AG","001 6","RELAGR","001","AGRTYPE","001 72969","001","AGRTYPE","","ENSTATS","1962/06/01 00:00:00","073","RELAMEND","0",0.0000,"","AGRCANCL",""
"AG","001 7","HDR","001","AGRTYPE","5","ENSTATS","2010/10/13 19:30:35","1972/06/01 00:00:00","","WORKFLOWSTREAM","N","A"
"AG","001 7","DREP","8083385","001"
"AG","001 7","PAR","8083385",20.0000000
"AG","001 7","PAR","1002769",80.0000000
"AG","001 7","LTRD","2010/10/12 00:00:00","N"
"AG","001 7","TERM","1962/06/01 00:00:00",10,0,0,"1972/06/01 00:00:00","","0","N","PRIMARY"
"AG","001 7","RENT","2011/06/01 00:00:00","",896.00,256.0000,256.0000,"Y"
[Updated on: Thu, 27 January 2011 12:49] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 11:29:05 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
