How to use external table feature to load .dat file

Question

I wish to import .dat file using oracle external table feature. File Format is as follows and file name test_210228053753.dat

00000000000005000.00517081233434 28/02/2021 781750202Claim Benefit Payout 781344 00000000000002500.00517081233431 28/02/2021 825229202Claim Benefit Payout 825401 00000000000060000.00517081233433 28/02/2021 452211023Claim Benefit Payout 452001 00000000000075000.00517081233432 28/02/2021 411002002Claim Benefit Payout 411015

Request you to help...

Thanks in advance.

Can't help you with the actual rules, as this file format doesn't really tell us anything. That said, any external table implementation will require the assistance of your DBA. What is your use case and where are you loading the data from (i.e. a network client, or from the DB server itself)? Do you have access to directories on the database server, DBA privileges in the database, or the ability to create DIRECTORY objects? — pmdba, Mar 01 '21 at 12:44
The Oracle documentation offers an entire section on External Tables. It should give you a better starting point that asking a general question on this site. [Find it here (link)](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-external-tables.html#GUID-038ED956-A6EE-4C6D-B7C9-0D406B8088B6). Also, please bear in mind that most problems with external tables are **system configuration** issues, and your DBA will is better placed to help you than we are. — APC, Mar 01 '21 at 14:04

score 1 · Answer 1 · answered Mar 01 '21 at 19:59

Step by step:

As a privileged user (SYS), create a directory (Oracle object which points to filesystem directory which contains the input data file). Grant privileges to user which will be reading that file.

SQL> show user
USER is "SYS"
SQL> create or replace directory ext_dir as 'c:\temp';

Directory created.

SQL> grant read, write on directory ext_dir to scott;

Grant succeeded.

Connected as SCOTT, I'm creating an external table which will select data from the input file. Sample data you posted suggests that its columns are fixed in size. If you meant to say that they are space delimited, well - they most probably aren't, because "Claim Benefit Payout" can't be loaded in that case (unless it is, actually, 3 different columns). As you didn't explain it, I'll consider its columns are fixed in size.

SQL> connect scott/tiger
Connected.

SQL> create table test1
  2    (col1    char(35),
  3     col2    char(10),
  4     col3    char(10),
  5     col4    char(20),
  6     col5    char(10)
  7    )
  8  organization external
  9    (type oracle_loader
 10     default directory ext_dir
 11     access parameters
 12       (records delimited by newline
 13        fields ldrtrim
 14          (col1  ( 1:32)   char,
 15           col2  (34:43)   char,
 16           col3  (45:53)   char,
 17           col4  (54:73)   char,
 18           col5  (75:80)   char
 19          )
 20       )
 21     location ('test1.txt')
 22    )
 23  reject limit unlimited;

Table created.

Does it work?

SQL> select * From test1;

COL1                                COL2       COL3       COL4                 COL5
----------------------------------- ---------- ---------- -------------------- ----------
00000000000005000.00517081233434    28/02/2021 781750202  Claim Benefit Payout 781344
00000000000002500.00517081233431    28/02/2021 825229202  Claim Benefit Payout 825401
00000000000060000.00517081233433    28/02/2021 452211023  Claim Benefit Payout 452001
00000000000075000.00517081233432    28/02/2021 411002002  Claim Benefit Payout 411015

SQL>

hi,thank you for the reply....but i have to load .dat file...so do i need to replace location ('test1.txt') to location ('test1.dat')? — lobh, Mar 02 '21 at 04:06

How to use external table feature to load .dat file

1 Answers1