SQL*Loader Problem: Fixed-Format, Different Record Types

I was wondering if anybody who has any experience with SQL*Loader might be able to help us with this problem:

We are taking a flat file, fixed-format records of 100 characters, and reading the contents into the respective tables based on the value of the first character. One of the record types use the full 100 characters, while the other is only 82 characters, the remaining places being filled with blanks.

Our problems are with these shorter records; SQL*Loader just won't recognize any of them and put them into the proper table. The 100-character records are read in properly, but the shorter ones don't go anywhere. We are using Version 6, PC Version but will soon be converting to the Sun OS version.

Our work-around for the time being has been to separate the data from the flat file into files according to record type, and then strip all the filler characters from the end of each record. Obviously, this will not work in a production environment. I know that an obvious solution is to specify that this source file be broken down into separate files by the originating system, but the external system producing this file is not under our control and can't be modified easily. We have tried numerous things in SQL*Loader, but it just seems that SQL*Loader doesn't like those filler spaces.

It seems to me that what we are trying to do is not an uncommon thing, and that the SQL*Loader product should be able to do this. If any of you could advise me as to whether this can be done or not, I would be much obliged. I have spoken with Oracle Tech Support, but all of the people I have spoken to are either unsure that this can be done, or else don't know or use SQL*Loader. Anyway, everyone knows that this group is where the REAL Oracle expertise is, right? :-)

I would really appreciate any suggestions you can offer me on this matter.

Here are the source statements for each of the SQL*Loader jobs. These are run one at a time, i.e. the source statements are not all embedded in the one source file.

INFILE tenitm.DAT
BADFILE TENDER.BAD
DISCARDFILE TENDER.DIS
REPLACE
INTO TABLE SYSTEM.TENDER
WHEN (1) = 'B'
(RECORD_TYPE_B POSITION(01:01) CHAR,

T_NUMBER        POSITION(02:11)     CHAR,
T_ENTITY        POSITION(12:15)     CHAR,
T_ANNUAL_CONTRACT   POSITION(16:16)  CHAR,
T_NBR_BUYER         POSITION(17:18)  CHAR,
T_DATE_CLOSE        POSITION(19:24)  CHAR,
T_CODE_COMMODITY    POSITION(25:27)  CHAR,
T_DATE_TENDER       POSITION(28:33)  CHAR,
T_DATE_COMPLETED    POSITION(34:39)  CHAR,
T_DATE_OPENING      POSITION(40:45)  CHAR,
T_DATE_ISSUED       POSITION(46:51)  CHAR,
T_DATE_LAST_CHG     POSITION(52:57)  CHAR,
T_AMT_TENDER        POSITION(58:66)  INTEGER EXTERNAL,
T_CODE_GPA_FILE     POSITION(67:75)  CHAR,
T_CODE_OPEN_TIME    POSITION(76:79)  CHAR,
ITEM_COUNT          POSITION(80:82)  INTEGER EXTERNAL)

LOAD DATA
INFILE tenitm.DAT
BADFILE ITEM.BAD
DISCARDFILE ITEM.DIS
REPLACE
INTO TABLE SYSTEM.ITEM
WHEN (1) = 'C'
(RECORD_TYPE_C POSITION(01:01) CHAR,

I_NBR_TENDER           POSITION(02:11)      CHAR,
I_CODE_ITEM_REQUIN     POSITION(12:14)      CHAR,
I_DESC_ITEM            POSITION(15:53)      CHAR,
SIGN_I_AMT_EXT         POSITION(54:54)      CHAR,
I_AMT_EXT              POSITION(55:65)      INTEGER EXTERNAL,
I_QTYITEM              POSITION(66:73)      INTEGER EXTERNAL,
I_QTY_ITEM_FRACTION    POSITION(74:76)      INTEGER EXTERNAL,
I_DESC_UNIT_OF_MEASURE POSITION(77:81)      CHAR,
SIGN_I_AMT_UNIT        POSITION(82:82)      CHAR,
I_AMT_UNIT             POSITION(83:92)      INTEGER EXTERNAL,
SIGN_I_AMT_UNIT_FRACTION  POSITION(93:93)   CHAR,
I_AMT_UNIT_FRACTION       POSITION(94:96)   INTEGER EXTERNAL,
I_SEQUENCE_NUMBER         POSITION(97:99)   INTEGER EXTERNAL)

The following is some sample data from the file TENITM.DAT:

BTP052585  05CLN02920403B96920319      920406920319920319000011536PO83209  1130046                  
CTP052585  1  Cab Assembly for 1984 GMC Brigadier,    0000000000000000001000      0000000000 000001
CTP052585     Model J9C064, Part # G15584841.         0000000000000000000000      0000000000 000002
CTP052585     (Serial # 1GDT9C4C7EV514149).           0000000000000000000000      0000000000 000003
CTP052585  C  Delivery required by April 10, 1992.    0000000000000000000000      0000000000 000004

Thanks again for your help!

Jamie Morry
The Enterprise Network
St. Johns, Newfoundland, Canada
Tel: (709) 729-7041
Fax: (709) 729-7039
jmorry_at_kean.ucs.mun.ca Received on Wed Jun 03 1992 - 15:11:50 CEST