Shulou(Shulou.com)06/01 Report--

In the previous article, we introduced the use of SQL*Loader and simple use cases of Oracle SQL*Loader (1). This article will interpret SQL*Loader in a deeper level according to the actual use cases.

Catalogue

The method of dealing with different files and formats by SQL*Loader

2.1 Excel Fil

The maximum number of lines in a general Excel file is no more than 65536 lines, indicating that the amount of data processing is not large. The way to deal with Excel is to save it as a CSV format file, and then you can import it in the normal way.

2.2 the files to be loaded are not separated by commas

There are two ways to refer to:

1) modify the data file to replace the delimiter with a comma.

2) modify the control file to change the value of FIELDS TERMINATED BY to the actual delimiter.

2.3 the data to be loaded contains delimiters

For example, to insert data into the scott.tb_loader table, the data format is as follows:

SMITH,CLEAK,3904

ALLEN, "SALER,M", 2891

WARD, "SALER,"S", 3128

KING,PRESIDENT,2523

To modify the control file, note the bold characters in the following sample code. The OPTIONALLY ENCLOSED BY parameter specifies that the delimiter is double quotation marks (the delimiter is double quotation marks by default in CSV format file, and you can modify the parameter value of OPTIONALLY according to the actual situation), as shown below:

-- Control file

[oracle@wjq SQL*Loader] $vim wjq_test2.ctl

LOAD DATA

INFILE'/ u01GU01 apprenticeship oracleUniplicationSQLQULAR LoaderUniver wjqcustomers test 2.dat'

TRUNCATE INTO TABLE tb_loader

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY'"'

(ENAME,JOB,SAL)

-- data file

[oracle@wjq SQL*Loader] $vim wjq_test2.dat

SMITH,CLEAK,3904

ALLEN, "SALER,M", 2891

WARD, "SALER,"S", 3128

KING,PRESIDENT,2523sqlldr

Run the above code, and the query results are as follows:

[oracle@wjq SQL*Loader] $sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test2.ctl

SQL*Loader: Release 11.2.0.4.0-Production on Tue Oct 31 14:56:40 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached-logical record count 4

-- query results

SCOTT@seiang11g > select * from tb_loader

ENAME JOB SAL COMM

--

SMITH CLEAK 3904

ALLEN SALER,M 2891

WARD SALER, "S" 3128

KING PRESIDENT 2523

2.4 data files do not have delimiters

The following data files are professionally called fixed-length strings, and it is easy to handle fixed-length strings in sqlldr. For this example, we modify the control file as follows:

-- Control file

[oracle@wjq SQL*Loader] $vim wjq_test3.ctl

LOAD DATA

INFILE'/ u01GU01 apprenticeship oracleUniplicationSQLQULAR LoaderUniver wjqharmtest3.dat'

TRUNCATE INTO TABLE tb_loader

(

ENAME position (1:5)

JOB position (10:18)

SAL position (23:26)

)

-- data file

[oracle@wjq SQL*Loader] $vim wjq_test3.dat

SMITH CLEAK 3904

ALLEN SALESMAN 2891

WARD SALESMAN 3128

KING PRESIDENT 252

The position keyword is used to specify the start and end position of a column. For example, JOB position (10:18) refers to the column value from the 10th character to the 18th character as the ENAME column. The writing of position is also very flexible, and the above functions can be changed into the following forms:

① position (* + 2:18): the way to specify a numeric value directly is called the absolute offset. If you use the * sign, the professional noun is called the relative offset, indicating where the last field ends, this time it starts, and the relative offset can also be calculated again. For example, Position (* + 2:15) means that it starts at + 2 where it ended last time.

② position (*) char (9): the advantage of this relative offset + type and length is that you only need to specify the start position for the first column, and the other columns only need to specify the column length, which is easier in practice.

Sqlldr runs as shown in the above code, and the query result is as follows:

-- sqlldr command

[oracle@wjq SQL*Loader] $sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test3.ctl

SQL*Loader: Release 11.2.0.4.0-Production on Tue Oct 31 15:04:13 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached-logical record count 4

-- query results

SCOTT@seiang11g > select * from tb_loader

ENAME JOB SAL COMM

--

SMITH CLEAK 3904

ALLEN SALESMAN 2891

WARD SALESMAN 3128

KING PRESIDENT 252

2.5 there are fewer columns in the data file than in the table to be imported

In the previous examples, the columns in the number file are demonstrated in an environment where there are fewer columns than in the table, which shows that the columns are not afraid, and the key is to look at the configuration in the control file. But what if the missing column has to be assigned? Just change the control file slightly, specify the COMM column directly, and assign an initial value of 0 (the data in ldr_case3.dat is still referenced here):

-- Control file

[oracle@wjq SQL*Loader] $vim wjq_test4.ctl

LOAD DATA

INFILE'/ u01GU01 apprenticeship oracleUniplicationSQLQULAR LoaderUniver wjqharmtest3.dat'

TRUNCATE INTO TABLE tb_loader

(

ENAME position (1:5)

JOB position (10:18)

SAL position (23:26)

COMM "0"

)

-- sqlldr command

[oracle@wjq SQL*Loader] $sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test4.ctl

SQL*Loader: Release 11.2.0.4.0-Production on Tue Oct 31 15:08:50 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached-logical record count 4

-- View the results

SCOTT@seiang11g > select * from tb_loader

ENAME JOB SAL COMM

--

SMITH CLEAK 3904 0

ALLEN SALESMAN 2891 0

WARD SALESMAN 3128 0

KING PRESIDENT 252 0

The value of COMM can also be determined according to the values of other columns. Modify the control file as follows

-- Control file

[oracle@wjq SQL*Loader] $vim wjq_test5.ctl

LOAD DATA

INFILE'/ u01GU01 apprenticeship oracleUniplicationSQLQULAR LoaderUniver wjqharmtest3.dat'

TRUNCATE INTO TABLE tb_loader

(

ENAME position (1:5)

JOB position (10:18)

SAL position (23:26)

COMM "substr (: SAL,1,1)"

)

Sqlldr executes the above code, and the result is as follows. It is obvious that the value of COMM is obtained from the first digit of the value of SAL.

-- sqlldr command

[oracle@wjq SQL*Loader] $sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test5.ctl

SQL*Loader: Release 11.2.0.4.0-Production on Tue Oct 31 15:12:00 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached-logical record count 4

-- execution result

SCOTT@seiang11g > select * from tb_loader

ENAME JOB SAL COMM

--

SMITH CLEAK 3904 3

ALLEN SALESMAN 2891 2

WARD SALESMAN 3128 3

KING PRESIDENT 252 2

Here, the value of the COMM column depends on the value of the SAL column. We use a function substr in SQL to take the first column of the SAL value and assign it to the COMM column. Of course, this is just an example. DBA can make appropriate modifications according to the actual needs. Many interesting transformations can be achieved through the functions in SQL, which may save you a lot of effort, and if the existing functions cannot be implemented, you can even write custom functions through PL/SQL. Then it is called in the control file of sqlldr, which is exactly the same as the system's own function, so that the columns to be loaded can be handled flexibly according to the requirements.

2.6 there are more columns in the data file than in the table to import

If there are fewer columns in the data file than in the table to be imported, it may be more troublesome to process, but it will be easier if there are more. Generally, there are two ways to deal with them according to different situations:

Method 1: modify the data file and delete the excess data, but when dealing with it in this way, it is also feasible for a small amount of data. Once the data file is large, hundreds or even gigabytes, it takes time and effort to modify the data file.

Method 2: use the control file FILLER in sqlldr to exclude unwanted columns

1) the demo data file is as follows

-- data file

[oracle@wjq SQL*Loader] $vim wjq_test6.dat

SMITH 7369 CLERK 1020 20

ALLEN 7499 SALESMAN 1930 30

WARD 7521 SALESMAN 1580 30

JONES 7566 MANAGER 3195 20

MARTIN 7654 SALESMAN 1580 30

BLAKE 7698 MANAGER 3180 30

CLARK 7782 MANAGER 2172 10

SCOTT 7788 ANALYST 3220 20

KING 7839 PRESIDENT 4722 10

TURNER 7844 SALESMAN 1830 30

ADAMS 7876 CLERK 1320 20

JAMES 7900 CLERK 1280 30

FORD 7902 ANALYST 3220 20

MILLER 7934 CLERK 1022 10

At this point, our requirements want us to import columns 1, 3, 4 and skip columns 2 and 5, and create the control file as follows

-- Control file

[oracle@wjq SQL*Loader] $vim wjq_test6.ctl

LOAD DATA

INFILE'/ u01GU01 apprenticeship oracleUniplicationsSQLroomLoaderPlacement wjqharmtest6.dat'

TRUNCATE INTO TABLE tb_loader

(

ENAME position (1:6)

COL1 FILLER position (10:13)

JOB position (17:25)

SAL position (28:31)

)

The FILLER keyword is supported when defining columns in sqlldr's control file, which can be used to specify filter columns. In the above control file, we use this keyword to filter columns, which means that the data between columns 10 and 13 are not imported.

In fact, because this is a fixed-length string, the position parameter we specified in the control file has limited what to read, and you can even delete the line TCOL FILLER position (10:13) in the control file.

Execute the sqlldr command:

[oracle@wjq SQL*Loader] $sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test6.ctl

SQL*Loader: Release 11.2.0.4.0-Production on Tue Oct 31 15:24:36 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached-logical record count 14

-- query results

SCOTT@seiang11g > select * from tb_loader

ENAME JOB SAL COMM

--

SMITH CLERK 1020

ALLEN SALESMAN 1930

WARD SALESMAN 1580

JONES MANAGER 3195

MARTIN SALESMAN 1580

BLAKE MANAGER 3180

CLARK MANAGER 2172

SCOTT ANALYST 3220

KING PRESIDENT 4722

TURNER SALESMAN 1830

ADAMS CLERK 1320

JAMES CLERK 1280

FORD ANALYST 3220

MILLER CLERK 1022

2) if the string in the data file is not in a fixed-length format, but is processed by a delimiter, you should pay attention to it in the control file, such as the data file as follows:

-- data file

[oracle@wjq SQL*Loader] $vim wjq_test7.dat

SMITH,7369,CLERK,1020,20

ALLEN,7499,SALESMAN,1930,30

WARD,7521,SALESMAN,1580,30

JONES,7566,MANAGER,3195,20

MARTIN,7654,SALESMAN,1580,30

BLAKE,7698,MANAGER,3180,30

CLARK,7782,MANAGER,2172,10

SCOTT,7788,ANALYST,3220,20

KING,7839,PRESIDENT,4722,10

TURNER,7844,SALESMAN,1830,30

ADAMS,7876,CLERK,1320,20

JAMES,7900,CLERK,1280,30

FORD,7902,ANALYST,3220,20

MILLER,7934,CLERK,1022,10

When creating a control file, the FILLER must be specified in the control file, otherwise the values in the column may not correspond to each other. The control file is created as follows

-- Control file

[oracle@wjq SQL*Loader] $vim wjq_test7.ctl

LOAD DATA

INFILE'/ u01apprt: oracle.oracle.sqqless test 7.dat'

TRUNCATE INTO TABLE tb_loader

FIELDS TERMINATED BY ","

(

ENAME,COL1 FILLER,JOB,SAL

)

Execute the sqlldr command and view the results

-- sqlldr command

[oracle@wjq SQL*Loader] $sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test7.ctl

SQL*Loader: Release 11.2.0.4.0-Production on Tue Oct 31 15:32:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached-logical record count 14

-- View the results

SCOTT@seiang11g > select * from tb_loader

ENAME JOB SAL COMM

--

SMITH CLERK 1020

ALLEN SALESMAN 1930

WARD SALESMAN 1580

JONES MANAGER 3195

MARTIN SALESMAN 1580

BLAKE MANAGER 3180

CLARK MANAGER 2172

SCOTT ANALYST 3220

KING PRESIDENT 4722

TURNER SALESMAN 1830

ADAMS CLERK 1320

JAMES CLERK 1280

FORD ANALYST 3220

MILLER CLERK 1022

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.