Solved

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

Posted on 2008-06-11
7
5,389 Views
Last Modified: 2012-05-05
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
Comment
  • 3
  • 2
  • 2
7 Comments
 
LVL 8

Expert Comment

by:srnar
ID: 21766854
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
 
LVL 47

Expert Comment

by:schwertner
ID: 21766951
You have to add "<sequence name>.nextval" against that column in the controlfile.
Give a try.
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 21767288
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 8

Expert Comment

by:srnar
ID: 21767391
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
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 22127278
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
 
LVL 47

Accepted Solution

by:
schwertner earned 125 total points
ID: 22127454
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
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
ID: 31466446
GOOD ONE
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now