SQL LOADER error summary,

SQL LOADER error summary,

When using SQL * LOADER to load data, there will always be various small problems due to the diversity of flat files and data formats, the following are some errors that may occur during the process of accumulating and sorting records. I hope it will be helpful to everyone. (Today, I suddenly saw my previous documents, So I sorted them out and summarized them into this blog. If I encounter other cases later, I will continue to add this article .)

ERROR 1: SQL * LOADER successfully loads data, but some fields are found to be garbled in Chinese. This is due to encoding. You can add character set encoding parameters to the control file to solve the problem,

For example: CHARACTERSET 'zhs16gbk' or CHARACTERSET 'utf8', set the character set of the database based on the actual situation of the database.

LOAD DATACHARACTERSET 'ZHS16GBK' INFILE '/oradata/impdata/test.txt' APPEND INTO TABLE ETL.TESTFIELDS TERMINATED BY '@#$' TRAILING NULLCOLS(MON_CD      , CITY_ID     ,CELL_ID   ,GPRS_USER_CNT  ,TERM_BRAND    ,BRAND_ID     ,FLUX      ,CELL_NAM    ) 

ERROR 2: The ORA-01722: invalid number ERROR is reported when loading data (not a general ERROR caused by a data type ERROR. But when the last field is of the NUMBER type, the above error will be reported.) due to the line break problem, if the NUMBER type column is located at the end of the table, there will actually be a line break (if it is \ n, no error occurs. If \ r \ n is used, an error is returned.) When you use SQLLDR to import data, the line break is also counted as part of the number, leading to an error. The solution is to add INTEGER or "terminated by whitespace ".

Record 1: Rejected-Error on table DM. TM_WGGHF_CELL_USER_DAY, column TYPE_ID.

ORA-01722: invalid number.

Note: If the data field type is NUMBER, using INTEGER will cause the loading data to be abnormal and 99.875000 will become a large INTEGER.

ERROR 3: When loading data, because there is a date field in it, you need to add a date function to process the data type in a specific format. Otherwise, the format will be disordered.

LOAD APPEND INTO TABLE ODS.TO_ALARM_LOGFIELDS TERMINATED BY '@#$' TRAILING NULLCOLS(COLLECT_DT            ,DATE_CD              ,HR_CD               ,DISPH_LOST_REASON         ,COLLET_TYPE_ID           ,ALM_TM        "TO_DATE(:ALM_TM,'DD-MM-YYYY HH24:MI:SS', 'NLS_DATE_LANGUAGE=American')"       ,ALM_DISCOVER_TM    "TO_DATE(:ALM_DISCOVER_TM, 'DD-MM-YYYY HH24:MI:SS', 'NLS_DATE_LANGUAGE=American')"  ,ALARM_DELSTA_ID          ,ALM_RESUME_TM     "TO_DATE(:ALM_RESUME_TM, 'DD-MM-YYYY HH24:MI:SS', 'NLS_DATE_LANGUAGE=American')"   ,FP_ALM_SER            ,FP3_ALM_FINGMARK         ,AREA_NAM              ,VSEQUIP_NAM            ,VSEQUIP_STATUS_ID         ,VSEQUIP_SUBSTATUS_ID        ,PLAN_DISPH_TM     "TO_DATE(:PLAN_DISPH_TM, 'DD-MM-YYYY HH24:MI:SS', 'NLS_DATE_LANGUAGE=American')"   ,AUTO_PRETREAT_STATUS_ID      ,EMOS_RECORD_CNT          ,CONT_TIME             ,ALM_CNT         ) 

ERROR 4 if some fields are missing in the data file, you can add constant parameters to the control file. For example, the following shows the data that lacks the COLLECT_DT field (which is actually generated based on the data file and some parameters ), you can solve this problem by using the CONSTANT parameter.

LOAD DATACHARACTERSET 'UTF8'INFILE 'DEVICE_WIRELESS_GSMCELL_F_20120130190002.CSV' "str '\r\n'"APPEND INTO TABLE STAGE.TS_RSRC_IRMS_GSMCELLFIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '\'' TRAILING NULLCOLS(    COLLECT_DT CONSTANT   '20120214',          CELL_ID           ,   CELL_NAM           ,    FULL_NAM            ,    OTHER_NAM  ,    OMC_CELL_NAM          ,    GROUP_NAM         ,    GROUP_NAM_EN         ) 

ERROR 5 if the line feed is in the format of WINDOW platform (\ r \ n, LINUX platform is \ n), as shown in (view in vi editor)

LOAD DATAINFILE 'EDS.TW_BUSS_GN_CELLFLUX_HR4.csv' "str '\r\n'"APPEND INTO TABLE EDS.TW_BUSS_GN_CELLFLUX_HR_TEST        FIELDS TERMINATED BY ',' TRAILING NULLCOLS (  DATE_CD    ,    HR_CD      ,    LAC_ID      ,   CELL_ID    ,    BUSI_TYP1_CD   ,    BUSI_TYP2_CD   ,   CITY_ID     ,    CELL_NAM     ,  UP_FLUX     ,   DOWN_FLUX    ,    VSD_CNT     ,   CI     ) 

ERROR 6 for the data file, see the appendix test.csv. The value of a field in the data file contains a line break. When loading data, you want to keep the data as it is, that is, after the data is imported into the database, there is a line break in the data. You can solve the problem through "str '\ r \ N.

As shown below, the data file test.csv has only two rows of data, and the second field in each record contains a line break.

12, "this is just

Test"

14. "data has

Line feed"

LOAD DATAINFILE 'TEST.csv' "str '\r\n'"APPEND INTO TABLE TESTFIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS(ID   ,NAME  )

ERROR 7: The field length exceeds 255

The default length of the field entered by SQLLDR is 255 characters. If the character length of a Field exceeds 255 and you do not specify the character length in the control file, the following error is reported: SQL Loader-Field in data file exceeds maximum length

ERROR 8: SQL * Loader-510 & SQL * Loader-2026

This is a special example I encountered. The SQLLDR loading log error is as follows:

SQL*Loader-510: Physical record in data file (/jkfile/DAD_CDR/TEMP/201207/EDS.TW_CUST_COSTCELL_HR_07.dat) is longer than the maximum(20971520)SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Because the EDS. TW_CUST_COSTCELL_HR_07.dat file is 37 GB, a lot of information has been searched and the problem has not been solved. In fact, it is found that the data file is damaged during FTP copy and transfer.

ERROR 9: SQL * Loader-605 & ORA-01653

ORA-01653: unable to extend table tablename by 128 in tablespace xxxxSQL*Loader-605: Non-data dependent ORACLE error occurred -- load discontinued.

This error occurs because the table's tablespace cannot be expanded, and the SQL * LOADER cannot insert data. The tablespace to be extended for the user

Articles you may be interested in:

• Oracle high-speed Batch Data Loading tool SQL * loader instructions
• [Oracle] common tool set SQL * Loader usage
• How to Use SqlLoader