Skip to Main Content
  • Questions
  • External Table referencing fixed width file with some short recs

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 30, 2004 - 3:48 pm UTC

Last updated: July 27, 2007 - 3:21 pm UTC

Version: 9

Viewed 10K+ times! This question is

You Asked

Thank you for all of your excellent work.

I created an External Table referencing a fixed width file that contains some short records.

The records are short because the line terminator was added immediately after the last field that contained non whitespace.

For example if a data file looked like:

field1field2field3
field1field2
field1

and the fields were defined like
(
col1 POSITION (1:6),
col2 POSITION (7:13),
col3 POSITION (14:20)
)


The second and third records would be rejected.

Is there a way (using EXTERNAL TABLE syntax) to allow the second record and third records to be loaded successfully (and the missing fields at the end of the file be set to null)?

Thank you.



and Tom said...

use missing field values are null:

ops$tkyte@ORA9IR2> create or replace directory datadir as '/tmp'
2 /

Directory created.

ops$tkyte@ORA9IR2> !echo xyz > /tmp/test.dat

ops$tkyte@ORA9IR2> !echo xy >>/tmp/test.dat

ops$tkyte@ORA9IR2> !echo x >>/tmp/test.dat

ops$tkyte@ORA9IR2> !od -c /tmp/test.dat
0000000 x y z \n x y \n x \n
0000011

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE "X"
2 (
3 X CHAR(1),
4 Y CHAR(1),
5 Z CHAR(1)
6 )
7 ORGANIZATION external
8 (
9 TYPE oracle_loader
10 DEFAULT DIRECTORY datadir
11 ACCESS PARAMETERS
12 (
13 RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
14 READSIZE 1048576
15 FIELDS LDRTRIM
16 MISSING FIELD VALUES ARE NULL
17 REJECT ROWS WITH ALL NULL FIELDS
18 (
19 X (1:1) CHAR(1),
20 Y (2:2) CHAR(1),
21 Z (3:3) CHAR(1)
22 )
23 )
24 location
25 (
26 'test.dat'
27 )
28 )REJECT LIMIT UNLIMITED
29 /

Table created.

ops$tkyte@ORA9IR2> select * from x;

X Y Z
- - -
x y z
x y
x




Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 30, 2004 - 8:11 pm UTC


Clear and concise

Alwyn, December 30, 2004 - 8:44 pm UTC


Perfect. Exactly what I needed.

A reader, January 03, 2005 - 11:03 am UTC

Thank you for your assitance.

Adding new column to fixed width external table

Shishir Mittal, July 27, 2007 - 10:55 am UTC

Hi Tom,

How can I add new columns to a fixed width external tables?
Tom Kyte
July 27, 2007 - 3:21 pm UTC

recreate the table, it is just metadata - a create table statement, you'll have to change the body of it to describe how to parse the new record format

Very helpful..Thanks!

A reader, November 17, 2013 - 4:51 am UTC


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library