-
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 :
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"
)
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|