Create external table in Oracle

Create Table extnl_table

(

Col01 varchar2 (100 ),

Col02 number,

......

)
Organization external
(Type oracle_loader
Default directory "XXX"
Access Parameters
(Records delimited by 0x '0a' skip 1 fields terminated by', 'enabledby' "'lrtrim missing field values are null reject rows with all null Fields
)
Location
("Cj_dir": 'data.txt'
)
)

;

 

The key to defining an External table is the Oracle loader parameter. If the records keyword is used to identify a data row and the fields keyword is used to define a field, it is commonly used as follows:

Records:

Delimited by 'xxx' -- line break. newline is often used to define line breaks. If a special character is used in the file, it must be defined separately. If it is a special symbol, you can use ox 'Sixteen bits ', for example, if the hexadecimal value of tab (\ t) is 9, it is delimited by 0x '09'; The hexadecimal value of Cr (\ r) is D, that is, delimited by 0x '0d '.

Skip X -- Skip x rows of data. In some files, the first row is the column name. to skip the first row, use skip 1.

Fields:

Terminated by 'X' -- field delimiter.

Enclosed by 'X' -- a field reference character. All data contained in this symbol is treated as a field. For example, a data row is in the format of "ABC", "a" "B," "C ,". After the terminated by ', 'enabledby' "' parameter is used, the system will read two fields. The value of the first field is ABC, and the value of the second field is a" B, "C ,.

Lrtrim -- delete white space characters.

Missing field values are null -- all vacancy values are set to null.

 

If the field length and delimiter of the External table file are uncertain, open the file. However, if the file size is very large, for example, it may take more than a dozen hours to open it with tools such as ultraedit. You can use the File splitting tool to cut the file into a small file about 1 MB, and then use NotePad to open the file and copy several lines of data for testing.

 

When reading External tables, the most common problem is that the buffer is insufficient. If the read size is increased, the problem may be caused by a line break error, or a line break error. Some line breaks cannot be seen in Notepad. For example, the Windows line breaks are \ n \ r, while the Mac and other systems are line feed (LF, 0x0a ). If you open a file with LF in windows, a black square is displayed in the notepad, and the correct line feed is displayed in the WordPad. Therefore, it is best to use ultraedit to open it and check the hexadecimal format directly.Code, Use delimited by 0x 'xx' to break the line.

 

It is also possible that when reading data from an external table, it is okay to read the previous data. If you read a certain number of rows, an error is returned. This may be caused by a row of data. You can only find a log file with a "bad" file name in the external file directory, where the error data is saved. Open notepad to check whether the error is in conflict with the definition of the External table.