Using Oracle External Tables: A Practical Guide

Oracle External Tables allow you to access external data, such as CSV files, directly from within the Oracle Database. In this guide, we’ll walk through setting up an external table, loading data from a CSV file, and performing SQL queries on that data.

Step 1: Prepare the Environment

1.1 Create a Directory on the Operating System

Start by creating a directory on your file system where the CSV file will be stored.

bashCopiar códigomkdir /u01/19c/oracle/external_table_dir

1.2 Create the CSV File

Next, create a sample CSV file with some data and save it in the directory you just created.

bashCopiar códigovi data.csv
--
id,name,age
1,John Doe,30
2,Jane Smith,25
3,Bob Johnson,40

Step 2: Set Up Oracle Database

2.1 Create an Oracle Directory

In Oracle SQL*Plus or any other database management tool, create an Oracle directory that points to the file system directory.

sqlCopiar códigoCREATE DIRECTORY ext_tab_dir AS '/u01/19c/oracle/external_table_dir';

2.2 Grant Privileges to the Directory

Grant read permissions on the Oracle directory to the appropriate user.

sqlCopiar códigoGRANT READ ON DIRECTORY ext_tab_dir TO your_user;

Step 3: Create the External Table

3.1 Define the External Table

Create the external table, specifying the file format and location.

sqlCopiar códigoCREATE TABLE external_table (
    id    CHAR(10),
    name  CHAR(50),
    age   CHAR(10)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_tab_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (
            id    CHAR(10),
            name  CHAR(50),
            age   CHAR(10)
        )
    )
    LOCATION ('data.csv')
)
REJECT LIMIT UNLIMITED;

This will create a table that reads directly from the data.csv file without loading the data permanently into the database.

Step 4: Query the External Table

4.1 Execute SQL Queries

You can now query the external table just like any other Oracle table.

sqlCopiar códigoSELECT * FROM external_table;

4.2 Perform Operations

You can perform SQL operations such as filtering and aggregation on the external data.

sqlCopiar códigoSELECT name, age
FROM external_table
WHERE age > 30;

Step 5: Maintenance and Best Practices

5.1 Monitor Errors

If there are any issues reading the file, check the rejection log for records that couldn’t be processed.

sqlCopiar códigoSELECT * FROM external_table_bad;

5.2 Update External Data

When the CSV file is updated, changes will be immediately available when querying the external table. No need to reload the data manually.

Conclusion

Oracle External Tables provide a powerful way to integrate external data without loading it permanently into the database. This guide demonstrated how to configure and use external tables effectively, allowing you to seamlessly query external files like CSVs.