When Using a SELECT Statement in a SQL*Loader EXPRESSION Clause, Data is Loaded When Using Conventional Path But Not When Using Direct Path
Last updated on AUGUST 08, 2017
Applies to:Oracle Database - Enterprise Edition - Version 18.104.22.168 and later
Information in this document applies to any platform.
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.
LOAD DATA INFILE '/scratch/truggier/dumps/teams.dat'
INTO TABLE TEAMS
FIELDS TERMINATED BY ','
TEAM_GROUND expression "(SELECT TEAM_GROUND FROM GROUNDS i WHERE i.TEAM_NAME = :TEAM_NAME)",
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms