[SOLVED] Why did SQL*Loader load 808594481 when using the INTEGER data-type?

Why did SQL*Loader load 808594481 when using the INTEGER data-type?

I was loading data using SQL*Loader and when making the control file I used the table definition and accidentally left the INTEGER data type on the "version" line.

And in the "version" field (data type integer) it inserted the value 808594481.

I'm having a hard time understanding how it processed this value -- I'm assuming it took it as a literal ... but is that the sum of the ASCII representations of each letter?

NOPE!

SELECT ASCII('I')+ascii('N')+ASCII('T')+ASCII('E')+ASCII('G')+ASCII('E')+ASCII('G')+ASCII('E')+ASCII('R') 
  FROM SYS.DUAL

returns 666 (which, btw is hilarious).

concatenate ascii values?

SELECT ASCII('I')||ascii('N')||ASCII('T')||ASCII('E')||ASCII('G')||ASCII('E')||ASCII('G')||ASCII('E')||ASCII('R') 
  FROM SYS.DUAL

returns 737884697169716982

I'm hoping someone out there knows the answer.

This is the actual control file:

OPTIONS (SKIP=1)
LOAD DATA
APPEND into table THETABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(id ,
parent_id ,
record_id ,
version INTEGER,
created_at ,
updated_at ,
created_by ,
updated_by ,
species_and_cohort ,
species_and_cohort_count)

Table DDL:

create table THETABLE
(
  id               VARCHAR2(36),
  parent_id        VARCHAR2(36),
  record_id        VARCHAR2(36),
  version                  INTEGER,
  created_at               VARCHAR2(25),
  updated_at               VARCHAR2(25),
  created_by               VARCHAR2(50),
  updated_by               VARCHAR2(50),
  species_and_cohort       VARCHAR2(150),
  species_and_cohort_other VARCHAR2(150),
  species_and_cohort_count NUMBER
)

Data:

id,parent_id,record_id,version,created_at,updated_at,created_by,updated_by,species_and_cohort,species_and_cohort_other,species_and_cohort_count
60D90F54-C5F2-47AF-951B-27A424EAE8E3,f9fe8a3b-3470-4caf-b0ba-3682a1c79731,f9fe8a3b-3470-4caf-b0ba-3682a1c79731,1,2014-09-23 21:02:54 UTC,2014-09-23 21:02:54 UTC,x@gmail.com,x@gmail.com,"PRCA Cherrylaurel,Sapling","",5
FC6A2120-AA0B-4238-A2F6-A6AEDD9B8202,f9fe8a3b-3470-4caf-b0ba-3682a1c79731,f9fe8a3b-3470-4caf-b0ba-3682a1c79731,1,2014-09-23 21:03:02 UTC,2014-09-23 21:03:02 UTC,x7@gmail.com,x7@gmail.com,"JUVI Eastern Redcedar,Sapling","",45

Solution

If you split 808594481 into bytes as it would be encoded in a 32 bit twos complement encoding, and treat each byte as an ascii-encoded character, you get "02,1" or "1,20" depending on byte order. You probably inserted a string that starts or ends with one of those, and some layer between your code and the database silently converted it to an integer.