Solved

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

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
database upgrade 8 73
How to free up undo space? 3 39
Process mapping 5 52
what privileges needed for S2 for this function (Oracle 12c)? 3 22
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

776 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