Sequence.Currval in SQLLDR
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 ?
got to use nextval at the start and the currval for all other records.
currval doesnt have a value until next value is called
How do I do that then ? I need to use nextval for the first record, and then currval for all the rest ?
Sorry, what I meant was how ?
How do I use one type of sequence value for record 1, and another for all others?
select sequence.nextval into 'variable' from dual
use the variable in your insert statements
Thanks again. Where do I put that select statement ? Here is my control file :
Replace into table table_table
FIELDS TERMINATED BY ','
(DATE_TIME "to_date(substr(ATE_TIME,1,14), 'YYYYMMDDHH24MISS')",
You might want to try a before insert trigger on the column you want to sequence.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width