Call trigger during SQL Loader insert

oraclesql-loader

I'm trying to insert data from a file into an Oracle table using SQL loader. The primary key is populated by a sequence where the nextVal is called from an insert trigger.

When I try using sqlldr, it seems to bypass the trigger as it says I cannot insert a null value into the primary key field.

Is there a way I can use sql loader to call the trigger for each insert?

OPTIONS (direct=false, SKIP=1)
load DATA
INFILE 'bcbsm.ctl'
append into table pprs.x
(
  LAST_NAME POSITION(1:25) CHAR
)

command: sqlldr username/password control=file.ctl

In my trigger there is a "Before Insert on x For Each Row" where I call the nextVal for the sequence then assign it to the primary key column. Since it's a unique identifier and populated by a sequence, it's not on the file that I'm trying to load.

Best Answer

Figured it out, I just have to put the sequence's nextVal in quotes in the control file:

append into [tablename]
(
   [column name] "[sequence name].nextVal"
   ...
)
Related Question