sql*loader variable length records identified by little endian unicode

[landolakes]

I am trying to load some variable length data using SQL*Loader. My control file is included. The only catch is that the 2 bytes that identify the logical record length, are little endian binary (Unicode) data. For example, for those 2 bytes to represent a record length of 101 characters, the first 2 bytes would be “01100101” and “00000000”, or the letter “e” and (null).

How can I get sqlldr to recognize the correct variable length of the logical record based upon those 2 bytes of data? Also, there are no line feeds, carriage returns, or anything else that would otherwise allow me to use the 'terminated by' syntax either.

I could not cut/paste in my source data as it contains null values (see above).

LOAD DATA
CHARACTERSET UTF16
LENGTH BYTE
BYTEORDER LITTLE
--BYTEORDERMARK NOCHECK

INFILE 'TEKTIPS.DAT' "var 2"


INTO TABLE "TEKTIPS"
APPEND
WHEN ADD_DEL_CODE = "1"

  (SEQID  "TEKTIPS_SEQ.NEXTVAL", 
   ADD_DEL_CODE POSITION(1:1)  CHAR, 
   MODEL_CODE  POSITION(2:7)  CHAR
)


INTO TABLE "TEKTIPS"
APPEND
WHEN ADD_DEL_CODE = "2"

  (SEQID  "TEKTIPS_SEQ.NEXTVAL", 
   ADD_DEL_CODE POSITION(1:1)  CHAR, 
   MODEL_CODE  POSITION(2:7)  CHAR, 
   MODEL_NBR  POSITION(8:10)  CHAR, 
   MODEL_NAME  POSITION(11:30)  CHAR, 
   MODEL_DESC  POSITION(31:80)  CHAR, 
   MODEL_DATE_FROM  POSITION(81:86)  CHAR, 
   MODEL_DATE_TO  POSITION(87:92)  CHAR, 
   MODEL_SEQ  POSITION(93:94)  CHAR, 
   MODEL_DATE  POSITION(95:100)  CHAR, 
   MODEL_FLAG  POSITION(101:101)  CHAR
)

 

[james777]

Try by changing the CHARCTERSET to UTF8, to see if it works.

 

[landolakes]

Same problems using UTF8.

As I continue to dig in further, I realized that the data is strictly just binary values, so the statement that they are "UniCode" is quite probably incorrect and misleading. I apologize for the inaccuracy, this is my first time dealing with any data other than straight US-ASCII.