When Using a SELECT Statement in a SQL*Loader EXPRESSION Clause, Data is Loaded When Using Conventional Path But Not When Using Direct Path (Doc ID 2287819.1)

Last updated on AUGUST 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

When loading data using the SQL*Loader utility with direct path, and the EXPRESSION clause contains a query that depends upon other database tables than the one being loaded, e.g.

OPTIONS (direct=true)
LOAD DATA INFILE '/scratch/truggier/dumps/teams.dat'
TRUNCATE
INTO TABLE TEAMS
FIELDS TERMINATED BY ','
trailing nullcols
(
TEAM_NAME,
TEAM_GROUND expression "(SELECT TEAM_GROUND FROM GROUNDS i WHERE i.TEAM_NAME = :TEAM_NAME)",
CAPACITY,
DIVISION
)

it is noticed that no data is loaded, and there are no errors. However, if line 'OPTIONS (direct=true)' is removed from the control file (meaning that conventional path is used), then all the data is loaded without issue.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms