Use Oracle external tables
by Vikas Kumar, Oracle Database Administrator, Rackspace Technology
Introduction
External tables receive data from the flat files stored outside of the database and allow Oracle® to query the data in those files. Oracle can parse any file format supported by the SQL\*Loader. You can't use data manipulation language (DML) on external tables, but you can use DML for query, join, and sort operations. You can also create views and synonyms against the external tables.
To take advantage of the external table feature, you should use the ORACLE_LOADER access driver and make sure the data files are in the text format. You should also understand Structured Query Language (SQL) so you can create an external table and perform queries against it.
Make an external table in an Oracle database
Create a directory
Oracle can parse any file format supported by the "SQL\*Loader." You can't use data manipulation language (DML) on external tables, but you can use DML for query, join, and sort operations. You can also create views and synonyms against the external tables.
To take advantage of the external table feature, you should use the ORACLE_LOADER access driver and make sure the data files are in the text format. You should also understand Structured Query Language (SQL) so you can create an external table and perform queries against it.
Make an external table in an Oracle database
Create a directory
Use the following code to create a directory that points to the location of the comma-separated value (CSV) or text files provided by a user.
create directory vk_dir as '/opt/oracle/app/admin/je1prd/mods/151214_CHG0004529';
Create an external table
Use the following SQL to create an external table:
create table kumarv5.VK_4529_ext
(
IBMCU NCHAR(12),
IBLITM NCHAR(25),
IBANPL NUMBER
)
organization external
(
type oracle_loader
default directory vk_dir
access parameters
(
records delimited by newline
logfile vk_dir:'VK_4529_ext.log'
badfile vk_dir:'VK_4529_ext.bad'
discardfile vk_dir:'VK_4529_ext.dcf'
skip 1
fields
terminated by '~'
missing field values are null
reject rows with all null fields
(
IBMCU,
IBLITM,
IBANPL
)
)
location
(
'data14.csv'
)
)
reject limit unlimited;
#### Create a local table
Because you can't perform DML on the external tables, use the following code to
create a local table based on the external one:
--create
create table kumarv5.VK_4529_int
(
IBMCU NCHAR(12),
IBLITM NCHAR(25),
IBANPL NUMBER
)
tablespace proddtat;
Copy data from the external to the local table
Use the following code to copy data from the external to the local table:
--insert
Insert into kumarv5.VK_4529_int select * from kumarv5.VK_4529_ext;
commit;
select count(*) from kumarv5.VK_4529_int;
Update the main production table
After you create the local table based on the external one, you can use the following code to update the main production table from the local table:
update PRODDTA.F43090 a
set a.PCMCU=' 1AM'
WHERE (a. IBMCU, a. IBLITM, a. IBANPL) in
(select b. IBMCU, b. IBLITM, b. IBANPL from kumarv5.VK_4529_int b );
Load data from external tables to regular tables
The following image shows you how to load data from an external table to a
regular table:
Conclusion
External tables enable you to access data from external sources as if it were in a table in the database. If users provide data in a spreadsheet or CSV format, and you need to update database tables based on the Excel sheet, an external table comes in handy.
Recent Posts
Platform Engineering: Revolutionizing Development Processes with the Adoption of Backstage
March 19th, 2024
The Latest Rackspace Technology Innovation in Platform Engineering: Time Saver for Backstage
February 29th, 2024
Rackspace Response to Microsoft February 2024 Patch Tuesday Vulnerability
February 19th, 2024
Take GitHub Webhooks Event Processing to the Next Level
January 19th, 2024
Choosing the Right Multicloud Model
January 5th, 2024