Oracle External Tables
Oracle SQL*Loader engine allows you to query external tables that are stored on flat files. When I say flat files, I literally mean a file that is stored on OS level. Yes, you can query a flat file that is stored outside of the database at OS level. The ORACLE_LOADER drive is used to query the external tables that is stored in any format on an external file.
Note: Any format means all the formats that SQL*Loader can read.
Note: you can only query external table. No DML operation is allowed.
Create flat files
For our example, let us create one file and save it as .txt format on the database server. Create my_regions.txt file and copy paste below contents
1,US
2,UK
3,AUS
4,IND
5,UAE
Save and close the file.
Create directory object
We need to create a directory inside Oracle database that points to the directory pointing to the location of my_regions.txt file
CREATE OR REPLACE DIRECTORY my_ext_tab AS '/home/oracle';
Create external table
Inside Oracle database, we need to create an external table that will query data from the above file
CREATE TABLE my_regions (
region_id number(1),
region_name varchar2(20)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY my_ext_tab
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
region_id CHAR(1),
region_name CHAR(5)
)
)
LOCATION ('my_regions.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;
Query external table
Once the external table is created, you can query it like a normal table
SELECT * FROM my_regions;
Create view on external table
Once you are able to query the external table, you can even create view on it
CREATE OR REPLACE VIEW my_regions_view AS
SELECT * FROM my_regions
WHERE region_name LIKE 'U%';
SELECT * FROM my_regions_view;
Load operation log
By default, a log of load operations is created in the same directory as the load files. In the same location where you saved my_regions.txt file, there will a log file created for the load operation
LOG file opened at 01/02/16 07:17:59
Field Definitions for table MY_REGIONS
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
REGION_ID CHAR (1)
Terminated by ","
Trim whitespace same as SQL Loader
REGION_NAME CHAR (5)
Terminated by ","
Trim whitespace same as SQL Loader
LOG file opened at 01/02/16 07:20:12
Field Definitions for table MY_REGIONS
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
REGION_ID CHAR (1)
Terminated by ","
Trim whitespace same as SQL Loader
REGION_NAME CHAR (5)
Terminated by ","
Trim whitespace same as SQL Loader