Skip to Main Content
  • Questions
  • Error on table PEGANBAM_EXTERNAL.self_service_id, ELISA_ID. Field in data file exceeds maximum length

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, padmini.

Asked: March 21, 2017 - 7:45 pm UTC

Last updated: March 23, 2017 - 11:40 am UTC

Version: 10.1.2

Viewed 1000+ times

You Asked


Error is :

Record 4: Rejected - Error on table SELF_SERVICE_ID_B, column "SSN".
ORA-01400: cannot insert NULL into ("PEGANBAM_EXTERNAL"."SELF_SERVICE_ID_B"."SSN")


Here is my control file below:

OPTIONS (

DIRECT = FALSE ,

PARALLEL = TRUE,

ERRORS = 2000,

SILENT=(FEEDBACK),

ROWS=28 )

LOAD DATA

CHARACTERSET AL32UTF8

INFILE '${DATA_DIR}/self_service_id.out'

BADFILE '${REJECT_DIR}/self_service_id.ldrreject'

DISCARDFILE '${REJECT_DIR}/self_service_id.discard'

APPEND

INTO TABLE SELF_SERVICE_ID

FIELDS TERMINATED BY '|' OPTIONALLY enclosed by '"'

TRAILING NULLCOLS (

"ELISA_ID" CHAR(1024),

"SSN" CHAR,

"INTERNAL_ID" CHAR,

"RECORD_SOURCE" CHAR )

Table structure in oracle :

CREATE TABLE "PEGANBAM_EXTERNAL"."SELF_SERVICE_ID_A"

(

"ELISA_ID" VARCHAR2(1024 BYTE) NOT NULL ENABLE,

"SSN" VARCHAR2(20 BYTE) NOT NULL ENABLE,

"INTERNAL_ID" VARCHAR2(40 BYTE),

"RECORD_SOURCE" VARCHAR2(16 BYTE)
) TABLESPACE "USERS" ;

CREATE INDEX "PEGANBAM_EXTERNAL"."SELF_SERVICE_ID_A_IDX1" ON "PEGANBAM_EXTERNAL"."SELF_SERVICE_ID_A" ("ELISA_ID")

TABLESPACE "USERS" ;

Trying to figure out since days.Please help me in getting this solved on urgent basis as I have to move it to production early .Thanks and really appreciate for help

and Connor said...

Isn't that error

ORA-01400: cannot insert NULL into ("PEGANBAM_EXTERNAL"."SELF_SERVICE_ID_B"."SSN")

fairly self explanatory ? When we parsed the incoming file, we ended up with a null for value for the SSN, and hence when we tried to insert that row into the table, it we rejected because SSN is a not null column.

Is this answer out of date? If it is, please let us know via a Comment