SQL Loader Error Summary _mssql

When using Sql*loader to load data, because of the variety of flat files and data format problems will always encounter a variety of small problems, the following is the accumulation of work, the records of some of the various errors encountered. Hope to be of some use to everyone. (Today, suddenly see their previous collation of these materials, and then slightly sorted, summed up into this blog, if the next encounter other cases, will continue to supplement this article. ）

ERROR 1:sql*loader load data successfully, but found that some of the fields in Chinese for garbled, this is because of the coding cause garbled. You can add character set encoding parameters to the control file to resolve the problem.

For example: CHARACTERSET ' ZHS16GBK ' or CHARACTERSET ' UTF8 ', setting the database character set according to the actual 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: When loading data, report ora-01722:invalid number error (not a general error caused by data type errors). This is the case when the last field is number type. Because of the line break, if the number Type column is at the end of the table, there will be a newline character (if \ n, there will be no error, if it is \ r \ n, the error will be reported), An error occurs when you import with Sqlldr a line break that counts as part of that number. Solution Plus integer or add "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 type, an integer will cause the loaded data to be abnormal, and 99.875000 will become a large integer.

Error 3: When loading data, you need to add date functions to handle data types in a particular format because there are date fields inside. Otherwise, there will be a pattern of confusion

 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_l Anguage=american ') ", alarm_delsta_id, Alm_resume_tm" to_date (: Alm_resume_tm, ' dd-mm-yyyy HH24:MI:SS ', ' N           

 Ls_date_language=american ') ", Fp_alm_ser, Fp3_alm_fingmark, Area_nam, Vsequip_nam , vsequip_status_id, vsequip_substatus_id, Plan_disph_tm "To_date (:P lan_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 from the data file, you can add constant parameters to the control file, for example, the following missing COLLECT_DT data (which is actually generated from the data file and some parameters) can be resolved by constant constant arguments.

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 break is the window platform format (that is, \ r \ n, the Linux platform is \ n), as shown in the following figure (see with the 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 data file See Attachment test.csv, the data file in a field in the value of a newline character, loading data, want to keep the data is the same, that is, after the data warehousing, the data stored in a newline. At this point you can solve the problem by "Str ' \ r \ n".

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

12, "This is just

Test

14, "The data has

Line Change "

LOAD DATA
INFILE ' test.csv ' "str ' \ r \ n '" "
APPEND into TABLE TEST
FIELDS terminated by ', '
optionally E nclosed by ' "' Trailing nullcols
(
ID   ,
NAME  
)

ERROR 7: Field length over 255

Sqlldr the field length entered by default is 255 characters. If a field has a character length of more than 255 and you do not specify its 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, Sqlldr mount log errors are 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 S QL Loader cannot continue.

Because this EDS.TW_CUST_COSTCELL_HR_07.dat file 37G, just started to search a lot of data, also did not solve the problem, in fact, the last discovery when the data file FTP copy transmission damage.

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.

The

This error occurs because the table space cannot be extended because the tablespace is not expanding, causing the Sql*loader mount data to not be inserted. To give the user the table space to extend the space