Oracle DBA Lesson 85 Part 1 — Express Loading with Explicit Context

SQL*Loader express mode derives a simple input mapping from an existing table's column definitions. It is useful when a regular delimited feed already matches the destination. You still need to choose a suitable load method, make the file location clear, and verify the destination and accepted records.

Oracle DBA Lesson 85 Part 1 — Express Loading with Explicit Context

This lesson uses the mapping, integrity, and restart concepts from Lesson 84.

How express mode obtains the mapping

Specifying TABLE activates express mode. You do not supply a control file. SQL*Loader uses the table columns in ALL_TAB_COLUMNS to infer field order and types. Express supports simple character, numeric, and datetime columns with delimited character input. Complex object types and irregular feeds need an appropriate alternative.

The example table is:

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

The headerless file departments_clean.csv contains:

40,Support
50,Engineering

Input position	Example value	Table column	Table type
1	40	DEPARTMENT_ID	NUMBER
2	Support	DEPARTMENT_NAME	VARCHAR2(80)

For the default field order, the first field supplies the identifier and the second supplies the name. The filename extension does not configure CSV parsing.

Setting	Express default or relevant behavior
Field delimiter	Comma. Override with TERMINATED_BY when supported and needed.
Record delimiter	Newline for the simple feed shown. Embedded terminators need the documented CSV settings.
Enclosures	No enclosing or optional enclosing character by default. Specify the appropriate enclosure option for quoted fields.
Field-name/header record	FIELD_NAMES=NONE. A header is not automatically recognized merely because the file ends in .csv.
Column order	Table column order. Supported FIELD_NAMES options can use a field-name record to determine order.
Data filename	If DATA is omitted, table-name.dat in the current directory.
Character set	Client/session character set defined by NLS_LANG, unless overridden with CHARACTERSET. Match it to the actual input encoding.
Dates/timestamps	NLS-derived defaults unless an appropriate format parameter is supplied.
Existing target rows	New rows are appended. Express does not deduplicate a previously accepted batch.

Inspect the table definition and the feed before using defaults. Express has its own supported parameter set. A parameter accepted by regular SQL*Loader is not automatically valid in express mode. Use a parameter file for values whose quoting becomes awkward, as Oracle recommends. Return to an explicit control file when the required mapping is clearer there. See SQL*Loader express mode and CREATE TABLE.

Choose the file reader and load method

Express mode defaults to external tables. In that path, database server processes access files through database DIRECTORY objects. The database needs READ on the input directory and WRITE on the output directory, and the server operating-system account needs filesystem access. A file present only on a separate client machine is insufficient.

For some errors, express mode can fall back from external tables to direct path. Inspect the actual execution messages rather than assuming the default was used. Do not grant broad directory-creation privileges just to make a convenience default work. For a deliberate external-table load, use narrowly scoped existing directories and the documented options. See external table concepts.

The client-file example uses these settings:

Parameter	Purpose
TABLE=load_express	Activates express mode and chooses the target. It does not create the target table.
DATA=departments_clean.csv	Chooses the actual input file rather than the table-derived .dat filename.
EXTERNAL_TABLE=NOT_USED	Bypasses the external-table option. This setting alone means conventional or direct loading. It does not establish which of those two was selected.
DIRECT=TRUE	Explicitly selects direct path. This parameter itself overrides the external-table default. Both settings are shown together so the chosen example is unambiguous.
LOG=load_express.log	Gives the utility log an explicit name.

The direct-path loader reads the client-accessible input and sends data to the target database. The source file remains on the client. Direct loading ultimately writes Oracle data blocks to physical database files on disk. The diagram's travelling record represents data transfer, not deletion or movement of the source file.

Direct-path restrictions from Lesson 84 still apply, including trigger, constraint, index, and access behavior. The example uses a simple empty table with no insert trigger or indexes. When conventional trigger processing is required, DIRECT=FALSE selects conventional path. Verify the resulting behavior and the log. Express convenience does not remove integrity or recovery requirements. See conventional and direct path loads.

Confirm the session context

app_user names the account and APP_SERVICE names the service. The service alias need not equal the PDB name. The account needs CREATE SESSION, CREATE TABLE, and quota for its own table. SQL*Loader must be installed on the client. The client must resolve the service, read the input, and write its local outputs. The explicit direct-path example needs no database directory object for that client input.

Connect and establish identity before creating the table:

sqlplus -L app_user@APP_SERVICE

Enter the password when prompted. In that session:

SELECT SYS_CONTEXT('USERENV','SESSION_USER') AS session_user,
       SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS current_schema,
       SYS_CONTEXT('USERENV','CON_NAME') AS con_name,
       SYS_CONTEXT('USERENV','SERVICE_NAME') AS service_name
FROM dual;

Returned value	Interpretation
SESSION_USER	The account that established this session.
CURRENT_SCHEMA	The current default schema for resolving unqualified objects.
CON_NAME	Current container in the multitenant database. Confirm the intended pluggable database.
SERVICE_NAME	Service associated with this connection.

Compare the returned values with the intended destination. A connection alias is a client-side name. Its spelling does not prove the connected container. Check the user and schema as well, because another schema can have a table with the same name. A mismatch means later statements would use a different container or schema. See SYS_CONTEXT.

Confirm LOAD_EXPRESS is unused, then create it with the definition above. Check column order, types, and the starting row count:

SELECT column_id, column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'LOAD_EXPRESS'
ORDER BY column_id;

SELECT COUNT(*) AS starting_rows FROM load_express;

The starting count is zero.

Run and verify the load

Save the two headerless records in the client's working directory, using a compatible encoding and line endings. From that directory:

sqlldr userid=app_user@APP_SERVICE \
  table=load_express \
  data=departments_clean.csv \
  external_table=not_used \
  direct=true \
  log=load_express.log
load_status=$?
printf '%s\n' "$load_status"

Enter the password at the prompt. Capture the exit status immediately. Later shell commands replace $?. Review a nonzero warning or error status deliberately, including in automation that uses set -e. Keep the input and each attempt's log and rejection evidence. A repeated log name can overwrite earlier evidence. Express-specific behavior remains governed by the express-mode reference. See SQL*Loader command-line reference.

Read the log's actual load method and its errors and warnings. Reconnect with the same account and service, repeat the session-context query, then inspect the table:

SELECT department_id, department_name
FROM load_express
ORDER BY department_id;

For the initially empty table and the valid input shown, the expected rows are:

DEPARTMENT_ID	DEPARTMENT_NAME
40	Support
50	Engineering

Reconcile the two input records with the loaded and rejected totals, the diagnostic messages, and the stored values. Explain every warning or unexpected row. A correct total alone cannot verify identifiers, names, or destination. Generated script text in the log cannot substitute for the execution messages and the stored rows.

Inspect indexes when the target has them:

SELECT index_name, status
FROM user_indexes
WHERE table_name = 'LOAD_EXPRESS';

The table definition creates no index, so that query returns no rows. For a nonpartitioned indexed target, investigate UNUSABLE and confirm the required indexes are VALID. Partitioned targets require the relevant partition and subpartition checks. Apply the prior lesson's trigger, constraint, and recovery checks when those features are present. See ALL_INDEXES.

Express appends to existing data. This table has no primary key, so rerunning the same load can produce duplicate rows. Retain batch identity and use a tested restart design for an operational feed. Do not infer a restart offset from the target row count.

Understand the generated material in the log

The express log contains generated control-file text and SQL scripts. They help explain the inferred mapping and can be adapted for regular SQL*Loader or a standalone external table. Oracle documents that these reference control and SQL scripts are not the scripts used by express mode itself. Interpret that material alongside the actual execution messages and the target contents. External-table SQL text in the log is not enough to claim that an external-table load ran. See SQL*Loader express mode.

For a manually managed external table, EXTERNAL_TABLE=GENERATE_ONLY can prepare statements for review and adaptation to existing directories. It does not perform the load. That approach has its own privileges, file location, and execution requirements. See external table concepts.

Quiz

1. How does express mode obtain the default input field order and types?

From the filename extension. From the target table column definitions. From alphabetical field-name order. From the first data row's inferred types.

2. Does every express-mode load read its input exclusively from the client host?

Yes, regardless of selected method. Yes, if the file extension is .csv. No; the default external-table method involves server-side directory access. No; express never supports client files.

3. What does EXTERNAL_TABLE=NOT_USED establish by itself?

External tables are bypassed; conventional or direct path is used. The destination table is created automatically. The load is guaranteed to be conventional path. The load is guaranteed to be direct path.

4. Which check best confirms the accepted result for the exercise?

Observe that the log file exists. Observe that the alias resembles the PDB name. Find generated SQL text in the log. Check actual session context, stored keys/names, record accounting and diagnostic messages.

5. What can happen when the same file is rerun against this populated table?

Express automatically deletes the previous batch. Express recognizes the filename and skips accepted records. Appending can duplicate rows because this table has no uniqueness protection. The table is always truncated automatically.

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