[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1912
  • Last Modified:

SQL LOADER ERROR

Hi,

I Face this error when I am try to load Data into Table through .CSV File

Record 1045: Rejected - Error on table TREQ_DBS, column SAFETYDETAILS.
Field in data file exceeds maximum length

I know there is a limit in Sqlloader
What  I Can add in the sqlldr Script File  then I am able to load Data

           ----      Table Structure    -----


CREATE TABLE TREQ_DBS             (
      REQUESTNO       NUMBER,
      SAFETYCODE       VARCHAR2(6),
      ENVTCODE                           VARCHAR2(6),
      SRED             VARCHAR2(6),
      MSDS             VARCHAR2(6),
      CHEMICAL                           VARCHAR2(40),
      MADE             VARCHAR2(3),
      CALENDAR_DATE       VARCHAR2(28),
      REQUESTOR        VARCHAR2(3),
      CELLTYPE                            VARCHAR2(3),
      CELLBATCH                            VARCHAR2(6),
      SUBBATCH                             VARCHAR2(1),
      CELLNO             NUMBER,
      PRIORITY                               VARCHAR2(6),
      PURPOSE                           VARCHAR2(250),
      NO_OFASSEM       NUMBER,
      CATHODE                            VARCHAR2(400),
      ANODE             VARCHAR2(400),
      ELEC              VARCHAR2(200),
      DESIGN             VARCHAR2(200),
      COND             VARCHAR2(3),
      PERFORM                            VARCHAR2(3),
      SAFETY             VARCHAR2(3),
      CONDDETAILS       VARCHAR2(400),
      PERFORMDETAILS       VARCHAR2(1200),
      NO_OFCYCLE       NUMBER,
      SAFETYDETAILS       VARCHAR2(1200),
      NO_OFABUSE       NUMBER,
      DATATECHNICIAN       VARCHAR2(30),
      COMMENTS                            VARCHAR2(200),
      THISTREQ                              VARCHAR2(100),
      EXPERIMENT_ID       NUMBER(10),
      APPROVED                              VARCHAR2(3),
      CONSTRAINT TREQ_PKY PRIMARY KEY(REQUESTNO)
USING INDEX TABLESPACE NDX )  

-------------------
 
         ---------------    SQL LOADER SCRIPT ----------------

load data
infile 'C:\pdxappdb_scripts\fadi_tables\csv\TREQ.csv'    
badfile 'C:\pdxappdb_scripts\fadi_tables\TREQ.bad'
discardfile 'C:\pdxappdb_scripts\fadi_tables\TREQ.txt'
replace
into table  TREQ_DBS
fields terminated by '$'
trailing nullcols
(
      REQUESTNO,
      SAFETYCODE,
      ENVTCODE,
      SRED,
      MSDS,
      CHEMICAL,
      MADE,
      CALENDAR_DATE,
      REQUESTOR,
      CELLTYPE,
      CELLBATCH,
      SUBBATCH,
      CELLNO,
      PRIORITY,
      PURPOSE,
      NO_OFASSEM,
      CATHODE,
      ANODE,
      ELEC,
      DESIGN,
      COND,
      PERFORM,
      SAFETY,
      CONDDETAILS,
      PERFORMDETAILS,
      NO_OFCYCLE,
      SAFETYDETAILS,
      NO_OFABUSE,
      DATATECHNICIAN,
      COMMENTS,
      THISTREQ,
      EXPERIMENT_ID,
      APPROVED

)


Thanks





0
patmoli
Asked:
patmoli
4 Solutions
 
Jinesh KamdarCommented:
Make ur SAFETYDETAILS column of the table to hold VARCHAR2(4000) and try reloading the data.
0
 
patmoliAuthor Commented:
hi jinesh_kamdar:

Still have same problem

bye
0
 
mohammadzahidCommented:
Check data in datafile(csv file) that is getting inserted in SAFETYDETAILS column.  Also, check if you can manually insert a row of data from csv file using conventional method - INSERT statement.

0
 
GGuzdziolCommented:
replace
     SAFETYCODE,
with
     SAFETYCODE "SUBSTR(SAFETYCODE, 1, <field length>)",
in your control file to trim the field, where <field length> is safetycode field length as defined in db (6 in your original post).
0
 
makhanCommented:
Hi,

Please post the contents of Row where you are getting the error in loading.

Regards,

Makhan
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now