SQL * LOADER error summary, sqlloader Summary

SQL * LOADER error summary, sqlloader 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 DATA

CHARACTERSET 'ZHS16GBK' 

INFILE '/oradata/impdata/test.txt' 

APPEND INTO TABLE ETL.TEST

FIELDS TERMINATED BY '@#$' TRAILING NULLCOLS

(

MON_CD          ,  

CITY_ID         ,

CELL_ID         ,

GPRS_USER_CNT   ,

TERM_BRAND      ,

BRAND_ID        ,

FLUX            ,

CELL_NAM       

)

ERROR 2:When loading data, the ORA-01722: invalid number error is reported (not a common 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_LOG

FIELDS 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 4If some fields are missing from 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 DATA

CHARACTERSET 'UTF8'

INFILE 'DEVICE_WIRELESS_GSMCELL_F_20120130190002.CSV' "str '\r\n'"

APPEND INTO TABLE STAGE.TS_RSRC_IRMS_GSMCELL

FIELDS 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 5If the line feed is in the format of the WINDOW platform (\ r \ n, LINUX platform is \ n), as shown in (view it in the vi editor)

LOAD DATA

INFILE '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 6For data files, 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 use"Str '\ r \ N '"Solve the problem.

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 DATA
INFILE 'TEST.csv '"str' \ r \ N '"
APPEND INTO TABLE TEST
Fields terminated ','
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 xxxx

SQL * 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