1

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.

  • Please show us your SQL*Loader control file and command line, as well as your table definition and some sample data. Which version of Oracle are you on? – Colin 't Hart Apr 21 '15 at 15:18
1

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"
   ...
)
|improve this answer|||||
  • Please mark your answer as correct so that others who have the same problem can find help. I know that it will take some time for your to be able to do this. – Vérace Apr 21 '15 at 17:42
  • Will do @Vérace – Raja Apr 21 '15 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.