Oracle DBA Lesson 84 Part 1 — Map a Feed and Account for Every Record

SQL*Loader uses a control file to interpret incoming file records and load values into table columns. A usable load is one whose records and stored values can be explained. A table containing rows does not establish that the complete feed was accepted.

Oracle DBA Lesson 84 Part 1 — Map a Feed and Account for Every Record

This part follows a small CSV from field mapping through numeric conversion, rejection and reconciliation. Part 2 covers conventional versus direct path and how to plan a restart after partial progress.

Follow the three-record feed

The example has no header and contains three logical records:

40,Support
50,Engineering
bad,Rejected number

The destination is an initially empty table with a numeric identifier and a text name. See CREATE TABLE.

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

The first two identifiers can be converted to numbers. The third is the word bad, which cannot become a numeric department identifier. For this input and mapping, expect two stored rows and one rejected record.

Describe the input with a control file

Save this as departments.ctl:

LOAD DATA
INFILE 'departments.csv'
INSERT INTO TABLE load_departments
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(department_id INTEGER EXTERNAL, department_name CHAR)

Clause	Meaning in this load
INFILE	Read departments.csv from the loader client's file system. Use a known working directory or explicit paths.
INSERT INTO TABLE	Load the named target, requiring it to be empty. A nonempty target causes this load to stop with an error. This SQL*Loader loading method is distinct from an ordinary SQL INSERT statement.
FIELDS TERMINATED BY ','	Separate incoming fields at commas.
OPTIONALLY ENCLOSED BY '"'	Allow a field value to be enclosed in double quotes. A delimiter inside an appropriately enclosed value is part of that value.
TRAILING NULLCOLS	Treat missing trailing, relatively positioned fields as null. It does not make a nonnumeric value valid. Nullability and other target constraints still apply.
Field list	Read the two fields in order and associate them with the named target columns.

INTEGER EXTERNAL describes an incoming numeric field represented by characters. It does not mean the CSV contains a native binary integer. The target NUMBER column stores the converted numeric value. CHAR describes incoming character data for the second field; the target column is VARCHAR2(80). SQL*Loader field types and database column types serve different purposes.

In spoken explanations, CHAR is “ka” and VARCHAR2 is “var ka two,” with the hard K sound in character. The identifiers above retain their exact spelling.

The short example omits CHARACTERSET, so the loader's applicable client character-set settings govern input interpretation. For a real feed, establish its encoding and configure loading accordingly. Check target column length semantics and byte/character limits when non-ASCII names are possible. Do not assume that every CSV with quoted multiline values has the same record handling as this simple file. See SQL*Loader control file reference and field list reference.

Run and keep the outcome

Invoke SQL*Loader from the directory that contains the CSV and the control file. app_user names the account and APP_SERVICE names the service. The account needs a session, CREATE TABLE, tablespace quota, and insert and select access to the target. The client needs SQL*Loader and the local input, control, log, and bad files.

sqlldr userid=app_user@APP_SERVICE \
  control=departments.ctl \
  log=departments.log \
  bad=departments.bad errors=10
load_status=$?
printf '%s\n' "$load_status"

Enter the password when prompted. The status must be saved immediately; a later shell command replaces $?. A job using set -e needs deliberate handling of SQL*Loader's warning status so the shell does not exit before the log and status are collected. Preserve each attempt's output under a distinct name or directory before trying again.

DIRECT defaults to FALSE, so this example uses conventional path. ERRORS=10 is the allowed insert-error limit. It does not repair records or redefine an incomplete feed as successful. An error limit, termination reason, or interrupted run can affect how much input was processed; check the actual log rather than inferring completion from the option.

For Linux and Unix, SQL*Loader documents these return codes:

Status	Meaning
0	All rows loaded successfully.
1	Command/syntax error or a nonrecoverable Oracle error for SQL*Loader.
2	Warning: rejected records, discarded records, or a discontinued load.
3	Fatal operating-system error.

A status of 2 is expected for this particular completed three-record test because one record is rejected. It does not, by itself, distinguish rejection from filtering or interruption. The log supplies that explanation. Shell success and business acceptance are separate decisions: even status 0 still requires the application's expected record and value checks. See SQL*Loader command-line reference.

Rejected versus discarded

Outcome	What happened	Evidence
Loaded	Field interpretation, conversion and insertion succeeded.	Target values and the table's load summary.
Rejected	SQL*Loader could not process the record correctly, or Oracle rejected insertion, for example because of conversion or a constraint.	Rejected input in the bad file and the error in the log.
Discarded	The record matched no applicable record-selection criteria, such as a control-file WHEN condition.	Discard count in the log; discarded input when a discard file is enabled and needed.

For the third record, retain bad,Rejected number in departments.bad and examine the log's conversion error for the identifier. The exact error wording can depend on client and load behavior; do not use a guessed error number as the acceptance test. Rejection of this record does not erase the two valid rows.

This control file has no selection filter, so the example has zero discarded records. A discard file is created only when enabled and needed; its absence alone does not certify a complete load. Do not call every unloaded record a discard. See SQL*Loader concepts.

Reconcile input, log and table

For this single-table, fully read test, with no header skip, selection filter or special record construction, the expected accounting is:

Measure	Expected
Logical records read	3
Rows successfully loaded into LOAD_DEPARTMENTS	2
Logical records rejected	1
Logical records discarded	0
Logical records skipped	0

3 read = 2 loaded + 1 rejected + 0 discarded. This is the accounting for the bounded example, not a universal equation for multitable loads, interrupted runs or arbitrary physical line counts. A logical record may be formed from multiple physical records. An expected feed total must include any records deliberately skipped before processing, such as a header.

Query the destination:

SELECT department_id, department_name
FROM load_departments
ORDER BY department_id;

Expected values:

DEPARTMENT_ID	DEPARTMENT_NAME
40	Support
50	Engineering

Check the identifiers and names, not only COUNT(*). A count of two with incorrect values is still wrong. Inspect the bad record and log reason, reconcile the expected source population, and apply any additional business rules before accepting the feed.

Already loaded data affects the next attempt. SQL*Loader can commit progress; a ROLLBACK in another SQL session cannot undo its committed rows. Re-running this same INSERT control against a now nonempty target fails the empty-table requirement. Changing it blindly to APPEND can duplicate valid rows, because this example has no uniqueness constraint. Plan correction and retry from actual load evidence; Part 2 develops that decision. See conventional and direct path loads.

Quiz

1. What does INTEGER EXTERNAL describe here?

A binary integer stored in the input file. An incoming numeric field represented by characters. A new database column type. A permission to ignore invalid identifiers.

2. Where should the third record go under this mapping?

Into the target with a null identifier automatically. Into a discard file because every failed row is discarded. Into the bad file as a rejected record, with the reason in the log. Nowhere, because SQL*Loader silently repairs it.

3. What causes a discarded record?

It matches no applicable record-selection criteria. Any numeric conversion error. Any existing row in the target. A Linux warning code by itself.

4. What does Linux status 2 prove by itself?

Every required record is accepted. Exactly one record has an invalid number. The target remains empty. A warning occurred; the log is needed to identify the outcome.

5. What should be checked before retrying the full file?

Only whether the command can start. Actual committed rows, load evidence, the correction and the retry strategy. Only the CSV's file name. Whether a different SQL session can roll back the loader's rows.

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