Sequence.Currval in SQLLDR

[citldba]

I want to assign every record from a particular file the same sequence number in a table, so that I can trace back which records came from which file (i.e. all records from the first file will have value '1', all from the next file will have value '2' etc.)

I will be loading many files to the table. I tried using a sequence with sequencename.currval in my control file, but got message

ORA-08002: sequence SEQUENCENAME.CURRVAL is not yet defined in this session

Is there anyway I can get a value assigned to CURRVAL that will remain the same for each record in the load, or is there some other clever way to do what I want to do ?

[davey23uk]

got to use nextval at the start and the currval for all other records.

currval doesnt have a value until next value is called

[citldba]

How do I do that then ? I need to use nextval for the first record, and then currval for all the rest ?

[davey23uk]

yes

[citldba]

Sorry, what I meant was how ?

How do I use one type of sequence value for record 1, and another for all others?

[davey23uk]

select sequence.nextval into 'variable' from dual

use the variable in your insert statements

[citldba]

Thanks again. Where do I put that select statement ? Here is my control file :

options(direct=false)
recoverable
Load Data
Replace into table table_table
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DATE_TIME "to_date(substr(ATE_TIME,1,14), 'YYYYMMDDHH24MISS')",
FIRST_FIELD,
SECOND_FIELD,
SEQ_FIELD "mysequence.nextval"
)

[PAVB]

You might want to try a before insert trigger on the column you want to sequence.