Oracle Sql*loader Detailed (5) Typical examples

This article describes the typical examples that sql*loader often use in the process of actual use.

1. How do I get fewer columns for the larger than data file in the table?

Suppose a CSV file is as follows:

A1,A2,A3,A4  
b1,b2,b3,b4  
c1,c2,c3,c4  
d1,d2,d3,d4

A total of 4 columns, the first request as long as the 2nd, 3 columns of data, how to do? At this point, the filler (note: not the filter) parameter comes in handy, the control file looks like this:

Load data  
infile ' data.csv ' "str ' \ r \ n '" into  
table test.test  
append  
fields terminated by ', '  
(  
  col1 Filler,  
  column01_name,  
  column02_name  
)

From the control file above we notice that there is a filler identifier behind the first column col1 that the data in column 1th will not be read; Careful readers may have discovered that we did not add a filler to column 4th because Sqlldr skipped the rest of the data by default, and of course if you write Col4 Filler is also a can.

2. How to generate sequence automatically?

It is really inconvenient that SQLLDR cannot use the sequence assignment in the database, only its own sequence statements.

Therefore, maintaining the original sequence requires the following steps:

1 First get the current value of the sequence on the table:

Sys@test16> Select Last_number from dba_sequences where sequence_name= ' Seq_order ';  
      
Last_number  
-----------  
    5410405

2 in the Sqlldr control file, the value obtained in the above step is the starting value of sequence, as follows:

ID SEQUENCE (5410405,1)

3 when the data is loaded, the maximum ID value is obtained with the SELECT statement.

4 Rebuild the sequence of the database, and set the initial value to the maximum ID of +1 obtained from the previous step.

3. What to do if the data file contains null values?

If some of the rows in the data file contain null values that cause the data in those rows to fail to import, then simply add trailing nullcols, which means null substitution if the corresponding value is not read, as follows:

Load data  
infile ' 663.csv ' "str ' \ r \ n '" into  
table p95169.zxj_663  
truncate  
fields terminated by ', '  
trailing Nullcols  
(  
hospdeptuuid,  
departmentname,  
sortcode01  
)