Solved

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

Posted on 2008-06-11
7
5,401 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can I rollback insert statements after commit in oracle? 7 57
TypeError: Users.save is not a function 3 30
SQL Query 34 80
Update from TABLE-A to TABLE-B 5 34
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…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

914 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

12 Experts available now in Live!

Get 1:1 Help Now