Oracle外部表:高级平面文件布局 [英] Oracle External Tables: Advanced Flat File Layout

问题描述

我希望在Oracle数据库中创建一个外部表，从服务器上的平面文件检索其数据.该文件的格式很重要.该文件中的每一行都可以是几种不同的布局之一，具体取决于行的前缀(前缀本身始终是固定长度).例如，以'TYPE1'开头的行的布局将不同于以'TYPE2'开头的行.

I wish to create an external table in an Oracle database, retrieving its data from a flat file on the server. The format of this file is non-trivial. Each line in this file can be one of several different layouts, depending on the line's prefix (the prefix itself is always a fixed length). For example, a line beginning with 'TYPE1' would have a different layout than a line beginning with 'TYPE2'.

我已阅读到外部表可以利用SQL Loader的控制文件可用的所有构造.但是，我阅读过的所有文档都仅涉及接缝，以处理琐碎的平面文件布局，由此所有行共享同一布局.一个SQL Loader控制文件可以使用WHEN子句轻松地处理这种情况:

I have read that external tables can take advantage of all the constructs made available to SQLLoader's control files. However, any documentation I have read only seams to deal with trivial flat-file layouts whereby all lines share a common layout. A SQLLoader control file could easily handle this scenario using the WHEN clause:

WHEN (1:5) = 'TYPE1'
(
    field1 POSITION(10:18),
    field2 POSITION(26:35)
)
WHEN (1:5) = 'TYPE2'
(
    field1 POSITION(23:27),
    field2 POSITION(15:19)
)

如何使用Oracle的外部表定义语法来表示这种布局?

How can I express such a layout using Oracle's external table definition syntax?

推荐答案

这是来自9.2文档，但您需要LOAD WHEN子句.

This is from 9.2 docs but you need the LOAD WHEN clause.

http://download.oracle.com /docs/cd/B10500_01/server.920/a96652/ch12.htm

这篇关于Oracle外部表:高级平面文件布局的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！