Oracle DBA Lesson 90 Part 1 — Query a server-side CSV with an external table

Map a text file to typed SQL columns, inspect the resulting rows, distinguish parsing failures from business-rule failures, and explain who manages the source file after the external table is dropped.

Oracle DBA Lesson 90 Part 1 — Query a server-side CSV with an external table

The table definition and the file

An external table keeps its column and access definitions in the database while the source data remains in a file outside database datafiles. The ORACLE_LOADER access driver reads text records and converts their fields to the declared column types when a query accesses them. That query is a way to inspect a feed before loading accepted rows into a regular database table.

Server file, directory object, and location

The file must be accessible to the database server process. A copy that exists only on a client laptop is outside this access driver's reach.

A database DIRECTORY object is an alias for a server-side operating-system folder. Creating the object stores that alias. The operating-system folder and the file must already exist, with permissions that let the database server process read the data and write the configured diagnostic files. Database directory-object privileges and operating-system permissions are separate requirements.

FEED_DIR is the directory object for this mapping. The schema that queries the table needs explicit READ and WRITE on it. Read access permits reading the CSV. Write access is required for the default log and bad files. Creating a directory object requires a privilege such as CREATE ANY DIRECTORY. Check the mapping, then grant access. In the grant, app_user stands for the schema that will query the table:

SELECT directory_name, directory_path
FROM all_directories
WHERE directory_name = 'FEED_DIR';

GRANT READ, WRITE ON DIRECTORY feed_dir TO app_user;

The GRANT is an administrator step. Inspect the mapping before placing files in that folder. LOCATION names the file inside the default directory. See external tables concepts and CREATE DIRECTORY.

Create the mapping

Place departments_clean.csv in the server folder with exactly these two lines and no header row:

40,Support
50,Engineering

The schema needs permission to create its own table. This definition maps the two fields:

CREATE TABLE departments_ext (
  department_id   NUMBER,
  department_name VARCHAR2(80)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY feed_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (department_id CHAR, department_name CHAR)
  )
  LOCATION ('departments_clean.csv')
)
REJECT LIMIT 0;

The visible definition in the video is split into excerpts. The ellipsis there stands for the full access-parameter section shown here.

• NUMBER and VARCHAR2(80) are the SQL column types. This small ASCII fixture fits the name column regardless of the session's character-length setting.
• ORACLE_LOADER interprets the text file. The two CHAR fields in the access parameters describe character fields in the input. Their contents are converted to the matching SQL columns. The identifier column remains the declared SQL NUMBER type.
• RECORDS DELIMITED BY NEWLINE makes each line a record. Commas separate fields, and optional double quotes allow an enclosed field. The clauses describe that simple format.
• MISSING FIELD VALUES ARE NULL permits a missing field to become NULL. A record with only the identifier can leave the name null. A required-name business rule still has to be checked.
• REJECT LIMIT 0 allows zero conversion errors. The first conversion error exceeds that limit and aborts the query. The default limit is also zero. A larger limit can return a successful query over the accepted records while other records are rejected. A successful query still has to be checked against every input record.

Creating the table stores metadata. Querying the mapped columns performs the file access and the conversion. See the ORACLE_LOADER access driver and CREATE TABLE.

Inspect typed rows and business rules

Read both columns and consume the full result:

SELECT department_id, department_name
FROM departments_ext
ORDER BY department_id;

For this clean file, the expected result is:

DEPARTMENT_ID	DEPARTMENT_NAME
40	Support
50	Engineering

ORDER BY makes the displayed order predictable. A client that fetches only the first page may leave the rest of the file unread. A count reports how many rows came back and leaves the field values and business rules unchecked. Oracle's default column-processing mode processes all defined columns. A configuration that processes only referenced columns can change which conversion problems a query encounters. Keep both columns in the inspection.

Check required values:

SELECT department_id, department_name
FROM departments_ext
WHERE department_id IS NULL OR department_name IS NULL;

Expected for the clean file: no rows. When a missing name becomes null, this query identifies it even though parsing succeeded. Match required-value and whitespace rules to the feed contract.

Check duplicate identifiers:

SELECT department_id, COUNT(*)
FROM departments_ext
GROUP BY department_id
HAVING COUNT(*) > 1;

Each group represents one identifier. A count above one identifies repeated values. Expected for the clean file: no duplicate groups. A null identifier is covered by the required-value check above. Reconcile the expected input record count, the accepted records, and the rejected records, together with required values, uniqueness, and any domain rules. Keep the file unchanged across those checks and the later load, so every statement reads the same contents.

Investigate a conversion failure

Use a separate file named departments_bad.csv:

X,Support
50,Engineering

Point the same external table at that file, then query both columns and consume the result:

ALTER TABLE departments_ext LOCATION ('departments_bad.csv');

SELECT department_id, department_name
FROM departments_ext
ORDER BY department_id;

X cannot convert to the declared NUMBER column. With REJECT LIMIT 0, the first conversion error aborts the query. Partial rows a client may already have displayed are not a complete valid result.

The log file contains driver diagnostic messages. The bad file contains records rejected for parsing or conversion. Unless another location is specified, both use the default directory. Oracle documents default names from the table name and the process identifier, such as DEPARTMENTS_EXT_%p.log and DEPARTMENTS_EXT_%p.bad. The driver expands %p. A bad file is created only when records are rejected. A log can append to an existing file. A bad file with the same generated name can be overwritten. Keep the diagnostics from the attempt you are reading, and keep other runs from reusing that name. A row that later fails a target-table constraint is a different failure from an access-driver rejection.

Restore the clean location:

ALTER TABLE departments_ext LOCATION ('departments_clean.csv');

Run the full result query and the validation queries again. Keep REJECT LIMIT 0 while the mapping or the feed is wrong. A higher limit can return a successful query that still omits rejected records. See the ORACLE_LOADER access driver.

Load accepted rows

After the file passes the agreed checks, insert the accepted rows into a regular table when the application needs updates, indexes, or ordinary database-managed storage. Create an empty target, then load it:

CREATE TABLE departments_stage (
  department_id NUMBER,
  department_name VARCHAR2(80)
);

INSERT INTO departments_stage (department_id, department_name)
SELECT department_id, department_name
FROM departments_ext;

The insert copies the query's rows into the regular table. The source file stays where it is. Check the inserted count and values before committing. ROLLBACK undoes an uncommitted insert. A second committed load of the same rows can duplicate them unless the load design prevents that. CREATE, ALTER, and DROP commit implicitly, including work that was still uncommitted. The target table can carry its own validation constraints and indexes. An external table cannot be indexed.

The CSV remains after the table is dropped

DROP TABLE departments_ext;
DROP TABLE departments_stage;

Drop departments_stage only when this load created it. Dropping an external table removes its database metadata. The CSV file remains. The file owner retains or removes that CSV and the diagnostic files. A shared directory object and any other feed stay in place. See DROP TABLE.

Quiz

1. Where must the CSV be accessible for this ORACLE_LOADER example?

Only on the SQL client's desktop. To the database server process through the supplied directory mapping and permissions. Inside the database's SYSTEM datafile. Inside the SQL statement itself.

2. What do the input CHAR fields and the table's NUMBER column mean?

Every SQL column becomes text. The file is imported permanently when the definition is created. The driver reads character fields and converts the identifier field to the declared SQL number type. Oracle ignores datatype conversion.

3. With REJECT LIMIT 0, what happens when the driver encounters the first conversion error?

The query aborts because the conversion-error limit is exceeded. The error is silently accepted as a valid number. The CSV file is deleted. The limit automatically becomes unlimited.

4. Why inspect required values and duplicate identifiers after parsing succeeds?

Parsing guarantees every business rule. A two-row count proves the identifiers are unique. Missing fields always abort every external query. Successfully parsed rows may still contain null required fields or repeated identifiers.

5. Does dropping this external table delete departments_clean.csv?

Yes, including all diagnostic files. No; it removes database metadata, while the file's owner manages the file separately. Yes, if the table has two columns. Only when REJECT LIMIT is zero.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily