External Table on file without terninator

Question

let's suppose I have a file like this:

101010AAAABBB

where I don't have a symbol for separating the different fields and I want to load it into an external table In the definition of my external table I would usually put

access parameters (records delimited by newline
fields terminated by ";"

if I had

101010;AAAA;BBB

But in my case, how can I write the syntax for loading every field in positional way?

Oracle version 10g

Thanks! Mark

score 1 · Answer 1 · answered Dec 22 '21 at 17:01

As you said - positional.

Sample test.txt file contents:

101010AAAABBB
555555CCCCDDD

External table:

SQL> create table tpos
  2    (col1 char(6),
  3     col2 char(4),
  4     col3 char(3)
  5    )
  6  organization external
  7    (type oracle_loader
  8     default directory ext_dir
  9     access parameters (records delimited by newline
 10                        fields ldrtrim
 11                       (col1 (1:6),
 12                        col2 (7:10),
 13                        col3 (11:13)
 14                       ))
 15     location ('test.txt'))
 16     reject limit unlimited;

Table created.

SQL> select * From tpos;

COL1   COL2 COL
------ ---- ---
101010 AAAA BBB
555555 CCCC DDD

SQL>

External Table on file without terninator

1 Answers1