Solved

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

Posted on 2008-06-11
7
5,442 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 48

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 48

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

820 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