Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5694
  • Last Modified:

How to use Sequence within the SQL loader while inserting the records in the table?

Hi,
   I have a table like Sample_tab. And i have Sequence like Sample_seq. This increment value of this sequnce is 1 and start with 1 and max value 99. My doubt is how can i insert the sequence number in the table while SQL loader?

Can anyone knows please let me know
0
Suriyaraj_Sudalaiappan
Asked:
Suriyaraj_Sudalaiappan
  • 3
  • 2
  • 2
1 Solution
 
srnarCommented:
You cannot use oracle sequences in sqlloader. But you can use external tables and there your sequence. External tables are easier to use - see example of Tom Kyte:

http://asktom.oracle.com/pls/asktom/f?p=100:11:3858901850071432::::P11_QUESTION_ID:6611962171229

Otherwise there is an option in sqlldr called SEQUENCE:

Causes SQL*Loader to generate a unique sequence number for each row that
is loaded. This is similar to, but not quite the same as, RECNUM. Sequence
numbers increment for each row that SQL*Loader attempts to load. This
includes rows that fail to load because they generate an error (those written to
the bad file). Sequence numbers do not increment for discarded or skipped
rows. By default, sequence numbers begin with 1 and increment by 1.

0
 
schwertnerCommented:
You have to add "<sequence name>.nextval" against that column in the controlfile.
Give a try.
0
 
Suriyaraj_SudalaiappanAuthor Commented:
Hi schwertner, you are correct. I am using the same way whatever you told. But i have one issue. For example I have inserted 14 rows by using sequence of this way. The records are also inserted. But while i give the SELECT <sequence name>.CURRVAL FROM DUAL. It is giving error like
ORA-08002: sequence XXCZ_VA_RV_MODEL_S.CURRVAL is not yet defined in this session.
         Why it is coming like this? But i am giving the below query, it is giving the last number is 15. I know that before you give nextval we can not give the currval. Once we gave NEXTVAL only the above query will work. But i have given the nextval through SQL loader right. Please let me know.
select last_number from user_sequences  
where sequence_name='sequence name'.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
srnarCommented:
Where are you putting Currval in sqlldr? You have to use Nextval by schwertner. One question: which version of database you are using?

Sqlldr of course opens a new session so the session cannot be aware of your currval.
0
 
Suriyaraj_SudalaiappanAuthor Commented:
hi sorry for the late reply. I am using oracle 9i. I have inerted the records through command promt by using SQL loader. What happened like after inserted i saw the currval for that sequence from the toad. But it throws the error. why? what is the reason?
0
 
schwertnerCommented:
Because the seqiece is NOCACHE (this is parameter of sequence).
To see CURVALUE the session has at least one time to issue NEXTVAL
against this sequence.
0
 
Suriyaraj_SudalaiappanAuthor Commented:
GOOD ONE
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now