Home » RDBMS Server » Server Utilities » SQL Loader - Skipping one column which shouldnot (SQL*Loader: Release 8.0.6.3.0 )
SQL Loader - Skipping one column which shouldnot [message #580567] Mon, 25 March 2013 21:34 Go to next message
bibsdash
Messages: 47
Registered: April 2008
Member
CONTROL FILE:
 LOAD DATA
     INFILE 'sample.txt'
     INSERT
     INTO TABLE TEST_RECORDS
     WHEN REC_TYPE_HDR='HDR'
     FIELDS TERMINATED BY '|' 
     TRAILING NULLCOLS
     ( 
       REC_TYPE_HDR   CHAR "LTRIM(RTRIM(:REC_TYPE_HDR))"
      ,HDR_DOC_TYPE     
      ,HDR_CMPY_NAME      
      ,HDR_DOC_DATE  date "MM/DD/YYYY HH24:MI:SS" 
      ,HDR_TRANS_ID 
     )
     INTO TABLE TEST_RECORDS
     WHEN REC_TYPE_DTL='DTL'
     FIELDS TERMINATED BY '|' 
     TRAILING NULLCOLS
    (
       REC_TYPE_DTL   POSITION(1:3) CHAR "LTRIM(RTRIM(:REC_TYPE_DTL))"
      ,TRAN_TYPE  CHAR "TRIM(:TRAN_TYPE)"  
	  ,LINE_COMP  CHAR "TRIM(:LINE_COMPANY)" 
    )


Data File:
HDR|RCPTCONFRM|XXXXXXX XXXXXXX|01/29/13 10:29:25|0070346508
PXR|603

After loading the data, the data for TRANS_TYPE is loading into LINE_COMP. So SQL Loader is skipping a field or column in the detail records.
 REC_TYPE_HDR HDR_DOC_TYPE HDR_CMPY_NAME        HDR_DOC_DATE               HDR_TRANS_ID       REC_TYPE_DTL     TRAN_TYPE LINE_COMP
 HDR          RCPTCONFRM    XXXXXXX XXXXXXX    1/29/0013 10:29:25 AM       70346508
                                                                                                     DTL                    603

The value 603 should have loaded into TRAN_TYPE field or column, instead it loaded into the next field or column LINE_COMP.
Please advice on how to fix it.

Thanks in advance.
Re: SQL Loader - Skipping one column which shouldnot [message #580570 is a reply to message #580567] Mon, 25 March 2013 22:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I am not really sure why, but using POSITION(1) instead of POSITION(1:3) seems to solve the problem. You also have a couple of other errors. LINE_COMPANY should be LINE_COMP and DTL should be PXR to load the data that you provided. Please see the revised control file below.

LOAD DATA
INFILE 'sample.txt'
INSERT
INTO TABLE TEST_RECORDS
WHEN REC_TYPE_HDR='HDR'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
REC_TYPE_HDR CHAR "LTRIM(RTRIM(:REC_TYPE_HDR))"
,HDR_DOC_TYPE
,HDR_CMPY_NAME
,HDR_DOC_DATE date "MM/DD/YYYY HH24:MI:SS"
,HDR_TRANS_ID
)
INTO TABLE TEST_RECORDS
WHEN REC_TYPE_DTL='PXR'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
REC_TYPE_DTL POSITION(1) CHAR "LTRIM(RTRIM(:REC_TYPE_DTL))"
,TRAN_TYPE CHAR "TRIM(:TRAN_TYPE)"
,LINE_COMP CHAR "TRIM(:LINE_COMP)"
)
Re: SQL Loader - Skipping one column which shouldnot [message #580572 is a reply to message #580570] Mon, 25 March 2013 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>(SQL*Loader: Release 8.0.6.3.0 )
REALLY, REALLY, Really, really OLD version which has been obsoleted & unsupported this whole Century.
Please join the 21st Century at your earliest convenience!
Re: SQL Loader - Skipping one column which shouldnot [message #580573 is a reply to message #580572] Mon, 25 March 2013 22:34 Go to previous message
bibsdash
Messages: 47
Registered: April 2008
Member
Thanks Barbara for pointing out.
It was a small thing I couldnot figure out.

Blackswan ,thanks for pointing out.
The client is on this old version.
Cannot complain much. Smile
Previous Topic: Rename Table while Using Datapump Import
Next Topic: Dump file determination [merged 2 by jd]
Goto Forum:
  


Current Time: Thu Mar 28 12:31:49 CDT 2024