Couple of days before i had an interesting discussion with my client related to Oracle external tables,
Client: Is there any way to read the data from flat file(s) which are stored outside the database?
Raja: Could you please explain in more detail?
Client: Sure. We have data analytics team, what they will do is, they will export the file(s) (.csv/.txt) from their system and push it to our file system (Oracle directory).
They have a job to push their files to our file system, which will run in predefined interval. Once it’s uploaded we should read the file(s) data and display it in our system.
Raja: Yes! it’s very much possible, if we go with Oracle external table.
Client: What is that?
Raja: It can be used for query, join and sort operations. External tables allow oracle to query data that is stored outside the database in flat files. Once it’s running we can create views against it.
Client: Oh! Sounds interesting. That’s what i needed. Is there any limitations on external tables?
Raja: Yes. No DML can be performed on external tables.
Client: That’s Ok. It’s not needed at all, since we are not going to perform any DML operation. So you please go ahead.
Here I am giving an example to read the data that is stored outside the database in flat files.
Step 1: File(s) should be saved to the file system available to the Oracle server.
Fig 1: Sample load file data (.CSV)
Fig 2: Sample load file data (.TXT)
Step 3: Understand the load file structure, which oracle external table going to read.
Step 4: Create the external table as per the load file structure.
CREATE TABLE fxgn_emp_ext_data
DEFAULT DIRECTORY APEXUAT — put your directory name here
ACCESS PARAMETERS (
fields terminated BY ‘,’ optionally enclosed BY ‘”‘
missing field VALUES are NULL
— put your file(s) name here, which should be saved in appropriate directory
REJECT LIMIT UNLIMITED;
Step 6: Query the external table the following result will be displayed.
Fig 3: Output
Note: Once external table is created, it can be queried like a regular table.
ORA-29913: If the load file have not been saved in the appropriate directory the following result will be displayed.
Fig 4: ORA-29913
That’s it. This is mind blowing.