Sequence.Currval in SQLLDR
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Sequence.Currval in SQLLDR

  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56

    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 ?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    got to use nextval at the start and the currval for all other records.

    currval doesnt have a value until next value is called

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56
    How do I do that then ? I need to use nextval for the first record, and then currval for all the rest ?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    yes

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56
    Sorry, what I meant was how ?

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

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    select sequence.nextval into 'variable' from dual

    use the variable in your insert statements

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    56
    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"
    )

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
  •  



Click Here to Expand Forum to Full Width