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

In the last two articles, we introduced the usage and use cases of SQL*Loader. This article will interpret SQL*Loader in a deeper level according to the actual use cases.

Oracle SQL*Loader use cases (1)

Oracle SQL*Loader use cases (2)

Catalogue

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

3.1 multiple data files, importing the same table

This is usually the case for systems with complex logic, because the exported data comes from multiple systems, so multiple data files may also be provided to DBA. In this case, multiple loads do not necessarily need to be performed, but only need to be properly configured in the control file. However, it is very important that the format of the data in the provided data file must be exactly the same.

Create a presentation table tb_ manager table

-- create a tb_manager table

SCOTT@seiang11g > create table tb_manager (mgrno number,mname varchar2 (30), job varchar2 (300), remark varchar2 (4000)

Table created.

There are multiple data files, one of which is as follows:

-- data File 1

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

10,SMITH,SALES MANAGER

11,ALLEN.W,TECH MANAGER

16,BLAKE,HR MANAGER

18,WJQ,TEACHER MASTER

-- data File 2

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

12,WARD,SERVICE MANAGER

13,TURNER,SELLS DIRECTOR

15,JAMES,HR DIRECTOR

-- data file 3

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

17,MILLER,PRESIDENT

Create a control file and set multiple INFILE parameters. The control file is as follows:

-- Control file

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

LOAD DATA

INFILE'/ u01GU01 apprenticeship oracleUniplicationsSQLroomLoaderPlacement wjqharmtest8

INFILE'/ u01GU01 apprenticeship oracleUniplicationsSQLroomLoaderPlacement wjqharmtest8room2.dat'

INFILE'/ u01GU01 apprenticeship oracleUniplicationsSQLroomLoaderPlacement wjqharmtest8% 3.dat'

TRUNCATE INTO TABLE tb_manager

FIELDS TERMINATED BY ","

(

MGRNO,MNAME,JOB

)

Execute the sqlldr command and view the results

-- execute sqlldr command

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

SQL*Loader: Release 11.2.0.4.0-Production on Tue Oct 31 15:45:43 2017

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

Commit point reached-logical record count 4

Commit point reached-logical record count 7

Commit point reached-logical record count 8

-- View the results

SCOTT@seiang11g > select * from tb_manager

MGRNO MNAME JOB REMARK

-

10 SMITH SALES MANAGER

11 ALLEN.W TECH MANAGER

16 BLAKE HR MANAGER

18 WJQ TEACHER MASTER

12 WARD SERVICE MANAGER

13 TURNER SELLS DIRECTOR

15 JAMES HR DIRECTOR

17 MILLER PRESIDENT

3.2 same data file, import different tables

The control file provides a variety of logic judgment methods. As long as the logic is clearly described, SQL*Loader can load according to the specified logic.

The data file is as follows:

-- data file

[oracle@wjq SQL*Loader] $cat wjq_test9.dat

BON SMITH CLEAK 3904

BON ALLEN SALER,M 2891

BON WARD SALER, "S" 3128

BON KING PRESIDENT 2523

MGR 10 SMITH SALES MANAGER

MGR 11 ALLEN.W TECH MANAGER

MGR 16 BLAKE HR MANAGER

TMP SMITH 7369 CLERK 1020 20

TMP ALLEN 7499 SALESMAN 1930 30

TMP WARD 7521 SALESMAN 1580 30

TMP JONES 7566 MANAGER 3195 20

The requirement is to import records starting with MGR into tb_manager table, records starting with BON into tb_loader table, and other records to be stored in obsolete files. The creation control file is as follows:

-- Control file

[oracle@wjq SQL*Loader] $cat wjq_test9.ctl

LOAD DATA

INFILE'/ u01GU01 apprenticeship oracleUniplicationsSQLroomLoaderPlacement wjqharmtest9.dat'

DISCARDFILE'/ u01GU01 apprenticeship oracleUniplicationSQLQULAR LoaderUniverse wjqroomtest9.dsc'

TRUNCATE

INTO TABLE tb_loader

WHEN TAB='BON'

(

TAB FILLER POSITION (1:3)

ENAME POSITION (6:10)

JOB POSITION (* + 1:20)

SAL POSITION (* + 3:27)

)

INTO TABLE tb_manager

WHEN TAB='MGR'

(

TAB FILLER POSITION (1:3)

MGRNO POSITION (6:7)

MNAME POSITION (9:15)

JOB POSITION (* + 2:30)

)

Although this control file looks more complex than the previous one, there is only one new syntax, the keyword, which is easy to understand through WHEN. At the same time, the DISCARDFILE parameter is specified to generate discarded files that do not meet the loading conditions. If you are interested, you might as well look at the wjq_test9.dsc file and wjq_test9.log file after executing the sqlldr command.

Also note that the WHEN logic judgment in the control file does not support the OR keyword, so if you have more than one judgment condition, you can only connect through AND instead of using OR directly.

Execute the sqlldr command and view the results

-- execute sqlldr command

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

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

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

Commit point reached-logical record count 11

-- View the 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

SCOTT@seiang11g > select * from tb_manager

MGRNO MNAME JOB REMARK

-

10 SMITH SALES MANAGER

11 ALLEN.W TECH MANAGER

16 BLAKE HR MANAGER

Post log log and obsolete log here

-- log log

[oracle@wjq SQL*Loader] $cat wjq_test9.log

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

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

Control File: / u01/app/oracle/SQL*Loader/wjq_test9.ctl

Data File: / u01/app/oracle/SQL*Loader/wjq_test9.dat

Bad File: / u01/app/oracle/SQL*Loader/wjq_test9.bad

Discard File: / u01/app/oracle/SQL*Loader/wjq_test9.dsc

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array: 64 rows, maximum of 256000 bytes

Continuation: none specified

Path used: Conventional

Table TB_LOADER, loaded when TAB = 0X424f4e (character 'BON')

Insert option in effect for this table: TRUNCATE

Column Name Position Len Term Encl Datatype

TAB 1:3 3 CHARACTER

(FILLER FIELD)

ENAME 6:10 5 CHARACTER

JOB NEXT+1:20 19 CHARACTER

SAL NEXT+3:27 24 CHARACTER

Table TB_MANAGER, loaded when TAB = 0X4d4752 (character 'MGR')

Insert option in effect for this table: TRUNCATE

Column Name Position Len Term Encl Datatype

TAB 1:3 3 CHARACTER

(FILLER FIELD)

MGRNO 6:7 2 CHARACTER

MNAME 9:15 7 CHARACTER

JOB NEXT+2:30 28 CHARACTER

Record 8: Discarded-failed all WHEN clauses.

Record 9: Discarded-failed all WHEN clauses.

Record 10: Discarded-failed all WHEN clauses.

Record 11: Discarded-failed all WHEN clauses.

Table TB_LOADER:

4 Rows successfully loaded.

0 Rows not loaded due to data errors.

7 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Table TB_MANAGER:

3 Rows successfully loaded.

0 Rows not loaded due to data errors.

8 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 7168 bytes (64 rows)

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 11

Total logical records rejected: 0

Total logical records discarded: 4

Run began on Tue Oct 31 15:55:48 2017

Run ended on Tue Oct 31 15:55:48 2017

Elapsed time was: 00:00:00.08

CPU time was: 00:00:00.02

-- discarded logs

[oracle@wjq SQL*Loader] $cat wjq_test9.dsc

TMP SMITH 7369 CLERK 1020 20

TMP ALLEN 7499 SALESMAN 1930 30

TMP WARD 7521 SALESMAN 1580 30

TMP JONES 7566 MANAGER 3195 20

3.3 the first N lines of the data file do not want to import

If one day you receive a data loading requirement, the user provides a 1 million-line data file and tells you to guide only 500000 lines. Congratulations, you have received a normal demand!

There are many ways to achieve, such as modifying the data file, only after 500000 lines (under Windows with the help of text tools such as EditPlus can be easily achieved, Linux/UNIX through TAIL and other commands can also be easily achieved), if you are very lazy, do not want to modify the file, that is just the taste of sqlldr, people early to provide a good SKIP parameters dedicated to meet such needs.

The data file is as follows:

-- data file

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

# This is data of emp

ENAME MGR JOB SAL

--

SMITH 7902 CLERK 1020

LEN 7698 SALESMAN 1930

▽ ARD 7698 SALESMAN 1580

JONES 7839 MANAGER 3195

MARTIN 7698 SALESMAN 1580

BLAKE 7839 MANAGER 3180

CLARK 7839 MANAGER 2172

SCOTT 7566 ANALYST 3220

KING PRESIDENT 4722

TURNER 7698 SALESMAN 1830

ADAMS 7788 CLERK 1320

JAMES 7698 CLERK 1280

FORD 7566 ANALYST 3220

MILLER 7782 CLERK 1022

We only import the data file from line 4, that is, the first three lines are not imported, and the control file is created as follows:

-- Control file

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

LOAD DATA

INFILE'/ u01GU01 apprenticeship oracleUniplicationsSQLroomLoaderPlacement wjqharmtest10.dat'

TRUNCATE INTO TABLE tb_loader

(

ENAME position (1:6)

XCOL FILLER position (13:16)

JOB position (18:26)

SAL position (32:35)

)

Execute the sqlldr command and view the results

-- execute sqlldr command

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

SQL*Loader: Release 11.2.0.4.0-Production on Tue Oct 31 16:12:11 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

If the user is demanding, specify that only the records from line XX to line XX are loaded. Sqlldr also has a parameter called LOAD, which can be easily achieved by configuring the LOAD parameter.

The above data file is still used here. The requirement is changed to import only the records in lines 4 to 9. We do not even need to modify the control file. We only need to add the LOAD parameter when executing sqlldr:

Execute the sqlldr command (that is, skip=3 skips the first three lines, load=6, and loads the next six lines of records) and view the result

-- execute sqlldr command

[oracle@wjq SQL*Loader] $sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test10.ctl skip=3 load=6

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

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

Commit point reached-logical record count 6

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

3.4 there are newline characters in the loaded data

Because the standard newline character is also a marker for sqlldr to identify the end of data lines, it is a little more complicated to load the data with newline characters into the table, and it needs to be handled according to the actual situation, and different situations are handled in different ways, but the basic idea is the same, that is, to indicate with sqlldr when the newline operation is needed.

3.4.1 manually specified newline characters

In the case of manually specifying a newline character, the newline character in the data file is not a standard newline flag, but an identity character (or multiple characters) defined by the user. The handling of this case is relatively simple, such as the data file as follows:

-- data file

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

10 nHe is a Sales Manager Smith MANAGER,This is SMITH.\ Sales.

11 nHe is a Tech Manager ALLEN.W. NHe is a Tech Manager.

16 MANAGER,This is BLAKE Blackie HR MANAGER,This is BLAKE.\ nHe is a Hr Manager.

Through the control file, we can process the data of the remark column before the data is loaded and replace the user-specified character with chr (10), that is, the standard newline character. The control file is created as follows:

-- Control file

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

LOAD DATA

INFILE'/ u01 apprenticespact oracleUniplicationsSQLroomLoaderPlacement wjqharmtest11and1.dat'

TRUNCATE INTO TABLE tb_manager

FIELDS TERMINATED BY ","

(

MGRNO

MNAME

JOB

REMARK "REPLACE (: remark,'\\ nChr (10))"

)

It is important to note here that "\ n" must be specified instead of just "\ n" when replacing, because "\ n" will be recognized by SQLLDR as a newline character and converted to a newline flag, which may cause data loading errors. Instead, the default escape character is specified, and when specified, sqlldr recognizes "\ n" as a normal character.

Execute the sqlldr command and view the results

-- execute sqlldr command

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

SQL*Loader: Release 11.2.0.4.0-Production on Tue Oct 31 16:21:04 2017

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

Commit point reached-logical record count 3

-- View the results

SCOTT@seiang11g > select * from tb_manager

MGRNO MNAME JOB REMARK

-

10 SMITH SALES MANAGER This is SMITH.

He is a Sales Manager.

11 ALLEN.W TECH MANAGER This is ALLEN.W.

He is a Tech Manager.

16 BLAKE HR MANAGER This is BLAKE.

He is a Hr Manager.

3.4.2 specify that the FIX property handles newline characters

The data file is as follows:

-- data file

[oracle@wjq SQL*Loader] $cat wjq_test11_2.dat

10 SMITH SALES MANAGER THIS IS SMITH.

HE IS A SALES MANAGER.

11 ALLEN.W TECH MANAGER THIS IS ALLEN.W

HE IS A TECH MANAGER.

16 BLAKE HR MANAGER THIS IS BLAKE.

HE IS A HR MANAGE.

(special note: because of the fixed length of FIX used, be sure to pay attention to the length of each row of data and replace it with insufficient spaces, otherwise an error will be reported when loading. The error message is as follows:

SQL*Loader-501: Unable to read file (wjq_test11_2.dat)

SQL*Loader-566: partial record found at end of datafile

SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

The reason for reporting an error is not that you cannot read the data file to be loaded. On the contrary, although there is an error message, it is possible that some of the data has been imported, because your existing data is not the length of your FIX, so you must pay attention to this when using FIX)

Create the control file as follows:

-- Control file

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

LOAD DATA

INFILE'/ u01 INFILE'/ u01 apprenticespact oracleUniplex SQL FIX LoaderPlacement wjqcustomers test112.dat' "SQL 65"

TRUNCATE INTO TABLE tb_manager

(

MGRNO POSITION (1:2)

MNAME POSITION (* + 1:10)

JOB POSITION (* + 1:24)

REMARK POSITION (* + 1:63)

)

FIX is an attribute of the INFILE keyword. INFILE has not only FIX attributes, but also VAR and STR attributes.

Execute the sqlldr command and view the results

-- execute sqlldr command

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

SQL*Loader: Release 11.2.0.4.0-Production on Wed Nov 1 10:37:45 2017

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

Commit point reached-logical record count 3

-- View the results

SCOTT@seiang11g > select * from tb_manager

MGRNO MNAME JOB REMARK

-

10 SMITH SALES MANAGER THIS IS SMITH.

HE IS A SALES MANAGER.

11 ALLEN.W TECH MANAGER THIS IS ALLEN.W.

HE IS A TECH MANAGER.

16 BLAKE HR MANAGER THIS IS BLAKE.

HE IS A HR MANAGER.

In fact, this method is to specify the length of each line through the FIX attribute before loading the data file (here 65 characters per line, including newline characters, the insufficient characters in this example are replaced by spaces, note the space at the end of the data file above). When the length is specified, the line breaks, regardless of whether there is a newline character in the middle, so it can only be used for data files with fixed-length strings, because only strings are fixed-length. You know what value to specify at INFILE.

For the above data file, you can also handle newline characters by using the CONCATENATE attribute to handle newline characters. The contents of the control file are as follows:

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

LOAD DATA

INFILE'/ u01 apprenticespact oracleUniplicationsSQLThe LoaderPlacement wjqroomtest11percent 2.dat'

CONCATENATE 2 INTO TABLE tb_manager

(

MGRNO POSITION (1:2)

MNAME POSITION (* + 1:10)

JOB POSITION (* + 1:24)

REMARK POSITION (* + 1:63) "REPLACE (upper (: REMARK), 'HE',chr (10) | |' HE')"

)

3.4.3 specify that the VAR property handles newline characters (line header identifies newline)

As mentioned earlier, the INFILE keyword also supports the VAR attribute. The syntax format is INFILE filename "var n". The value of n cannot exceed 40, otherwise an error will be reported. If n is not specified, the default value is 5.

This section demonstrates how to handle newline characters in this way. Overall, this is a really talented way to start by specifying a fixed-length string at the beginning of each line through the VAR attribute, which indicates the length of the line, which supports variable-length strings.

The data file is as follows:

-- data file

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

06110,SMITH,SALES MANAGER,This is SMITH.

He is a Sales Manager.

06311,ALLEN.W,TECH MANAGER,This is ALLEN.W.

He is a Tech Manager.

05516,BLAKE,HR MANAGER,This is BLAKE.

He is a Hr Manager.

The 061063055 at the beginning of each line in the data file indicates that the line takes 61 characters, 63 characters and 55 characters respectively.

Create the control file as follows:

-- Control file

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

LOAD DATA

INFILE'/ u01 INFILE'/ u01 apprenticespact oracleUniplicationsSQLQuest LoaderPlacement wjqharmtest11room3.dat' "var 3"

TRUNCATE INTO TABLE tb_manager

FIELDS TERMINATED BY ","

(MGRNO,MNAME,JOB,REMARK)

Execute the sqlldr command and view the results

-- execute sqlldr command

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

SQL*Loader: Release 11.2.0.4.0-Production on Wed Nov 1 09:51:12 2017

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

Commit point reached-logical record count 3

-- View the results

SCOTT@seiang11g > select * from tb_manager

MGRNO MNAME JOB REMARK

-

10 SMITH SALES MANAGER This is SMITH.

He is a Sales Manager.

11 ALLEN.W TECH MANAGER This is ALLEN.W.

He is a Tech Manager.

16 BLAKE HR MANAGER This is BLAKE.

He is a Hr Manager.

① can be seen as a variation of the second approach, which does not specify a row length at INFILE, but instead marks the length of each row at the beginning of each row to support variable-length data formats.

From a comprehensive point of view of ②, the practical value of these two methods is relatively low, not to mention whether users really have the patience to generate data files in this format. Even if users provide data files in this format, they should know that the recognition of line breaks is also different under windows and Linux/UNIX due to the operating system's own reasons. In Windows environment, the line feed flag consists of two bytes: "enter chr (13) + line feed chr (10)". The Linux/UNIX environment is "line wrap chr (10)" one byte, that is to say, the length is specified, and after the operating system changes, the length may have to change, which results in poor versatility of the data files provided by the user.

Is there an easier way to ③? In fact, there is no easy way, as mentioned in the previous content, you need to specify when to break lines for sqlldr, so it is necessary to preprocess the data files, but relatively speaking, the methods described below are easier to operate and more feasible.

3.4.4 specify that the STR property handles newline characters (line end identifies newline)

This method also requires processing the data file first, putting a mark on the newline of the record, such as "," (of course, it can be defined as other characters, but be careful not to conflict with the data to be imported), so that sqlldr will know that it is time for a new line when it sees the character.

Because of the high probability of a single character appearing in the imported data, it is recommended that the newline flag consists of as many characters as possible. It is usually customary to define "character + newline" as the new newline tag, which we also use here.

The data files and control files are as follows:

-- data file

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

10,SMITH,SALES MANAGER,This is SMITH.

He is a Sales Manager. |

11,ALLEN.W,TECH MANAGER,This is ALLEN.W.

He is a Tech Manager. |

16,BLAKE,HR MANAGER,This is BLAKE.

He is a Hr Manager. |

-- Control file

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

LOAD DATA

INFILE'/ u01 str str'/ u01 apprenticeship

TRUNCATE INTO TABLE tb_manager

FIELDS TERMINATED BY ","

(MGRNO,MNAME,JOB,REMARK)

Execute the sqlldr command and view the results

-- execute sqlldr command

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

SQL*Loader: Release 11.2.0.4.0-Production on Wed Nov 1 09:45:28 2017

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

Commit point reached-logical record count 3

-- View the results

SCOTT@seiang11g > select * from tb_manager

MGRNO MNAME JOB REMARK

-

10 SMITH SALES MANAGER This is SMITH.

He is a Sales Manager.

11 ALLEN.W TECH MANAGER This is ALLEN.W.

He is a Tech Manager.

16 BLAKE HR MANAGER This is BLAKE.

He is a Hr Manager.

Two ways of character assignment are supported in the STR attribute:

'char_string': normal characters are standard visible characters, but some invisible characters can be specified in string mode by the following backslash identification:

\ n: indicates a line break.

\ t: represents a line tab (tab).

\ f: indicates page change.

\ v: represents a column tab.

\ r: indicates enter.

At this point, we have to mention again the difference in newline recognition between Windows and Linux/UNIX. Specify "\ n" under Linux/UNIX and "\ r\ n" under Windows to indicate a complete newline character.

X characters hexagonal strings: binary characters. Some invisible characters, such as carriage returns and line feeds, can be converted to hexadecimal and then specified by str Xcarriage hexadecimal.

For example, if the functions in the above control file are expressed in binary characters, the form is as follows:

INFILE ldr_case11_4.dat "str Xero7C0A'"

To see the hexadecimal encoding of a specified character, you can generate it through UTL_RAW.CAST_TO_RAW, for example:

SCOTT@seiang11g > select utl_raw.cast_to_raw ('|'| | chr (10)) from dual

UTL_RAW.CAST_TO_RAW ('|'| | CHR (10))

7C0A

This approach is equivalent to customizing a newline flag, and the standard newline character no longer has a special meaning, just as part of the data to be loaded. The biggest advantage over the first method is that the data file is relatively easier to deal with, as long as a specified character is appended to the last column when the data file is generated, which is a small case for people with a little SQL foundation.

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.