Hello, I'm having an issue in creating an external table using a fixed width data file coming from a Windows environment. The file has CRLF (carriage return line feed) line terminating characters. For external tables that I've created that are comma delimited we've used RECORDS DELIMITED BY '\r\n' within the ACCESS PARAMETERS. That has worked perfectly because our database is on a unix machine. I tried doing this with this new table and the table is created fine, but when viewing the data it only returns the first row. It seems like Oracle expects the fixed width files to end with the platform's terminating characters. When I change the table to RECORDS DELIMITED BY NEWLINE and convert the file to unix (end only in LF (line feed)), the table works fine and all the records in the file display correctly. Having to manually convert the file from Windows to Unix is not ideal so that's why previous external tables used the '\r\n' in the ACCESS PARAMETER definition. Am I missing something in the table definition below for a fixed width data file ending with CRLF? I'm thinking it is treating '\r\n' as a string and not as an indicator that the line is ending in the file. I've tried changing it to use double quotes ("\r\n") with no change in behavior. Why would the '\r\n' work with comma delimited files but not with a fixed width files? Any and all help or insight is fully appreciated!
Below is the Oracle version we're using
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
CREATE TABLE AR_FRB_ACH_ROUT_NUM
(
ROUTING_NUM VARCHAR2(9),
OFIFICE_CODE VARCHAR2(1),
SERVICING_FRB_NUM VARCHAR2(9),
REC_TYPE_CODE VARCHAR2(1),
CHANGE_DATE DATE,
NEW_ROUTING_NUM VARCHAR2(9),
CUSTOMER_NAME VARCHAR2(36),
DELIVERY_ADDR VARCHAR2(36),
DELIVERY_CITY VARCHAR2(20),
DELIVERY_STATE VARCHAR2(2),
DELIVERY_ZIP VARCHAR2(5),
DELIVERY_ZIP_PLUS VARCHAR2(4),
PHONE_AREA VARCHAR2(3),
PHONE_PREFIX VARCHAR2(3),
PHONE_SUFFIX VARCHAR2(4),
INST_STATUS_CODE VARCHAR2(1),
DATA_VIEW_CODE VARCHAR2(1),
NOT_USED VARCHAR2(5)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY APPJOB_IN
ACCESS PARAMETERS
( RECORDS DELIMITED BY '\r\n'
LOGFILE 'arfrbachrout_log.dat'
DISCARDFILE 'arfrbachrout_disc.dat'
BADFILE 'arfrbachrout_bad.dat'
FIELDS
MISSING FIELD VALUES ARE NULL
(
ROUTING_NUM (1: 9) CHAR(9),
OFIFICE_CODE (10:10) CHAR(1),
SERVICING_FRB_NUM (11: 19) CHAR(9),
REC_TYPE_CODE (20: 20) CHAR(1),
CHANGE_DATE (21: 26) CHAR(6) DATE_FORMAT DATE MASK "MMDDYY",
NEW_ROUTING_NUM (27: 35) CHAR(9),
CUSTOMER_NAME (36: 71) CHAR(36),
DELIVERY_ADDR (72: 107) CHAR(36),
DELIVERY_CITY (108: 127) CHAR(20),
DELIVERY_STATE (128: 129) CHAR(2),
DELIVERY_ZIP (130: 134) CHAR(5),
DELIVERY_ZIP_PLUS (135: 138) CHAR(4),
PHONE_AREA (139: 141) CHAR(3),
PHONE_PREFIX (142: 144) CHAR(3),
PHONE_SUFFIX (145: 148) CHAR(4),
INST_STATUS_CODE (149: 149) CHAR(1),
DATA_VIEW_CODE (150: 150) CHAR(1),
NOT_USED (151: 155) CHAR(5)
)
)
LOCATION (APPJOB_IN:'FRBACHDir.txt')
)
REJECT LIMIT 0;