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 Go to next message
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 Go to previous messageGo to next message
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

Re: ?Trivial? sqlldr problem [message #490853 is a reply to message #490851] Thu, 27 January 2011 12:57 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
nice post & THANKS for the followup!
Previous Topic: imp Issue
Next Topic: Importing into lower version using (exp/imp)
Goto Forum:
  


Current Time: Fri Mar 29 09:18:32 CDT 2024