CODEWITHSUNDEEP
sqloracle-databaseplsql
neha
neha

Reputation: 3

Generate sequence number in select

How can we generate a sequence number which starts with 1001 and use in the select statement and lpad the sequence value with '0's .This for inserting records into CSV file from Db tables and this code is written in cursor . Can anyone suggest the best way to do this .I got an example query, will the query work ? 

select lpad((create sequence customer.seq as int start with 1001 increment by 1),11,0);

Upvotes: 0

Views: 27697

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

This query will generate a sequence of 10 digits starting from 1001 and padded with zeros.

select LPAD ( 1000 + LEVEL ,11,0 ) FROM DUAL CONNECT BY LEVEL <= 10;

output

   id_column
   ------------
    00000001001
    00000001002
    00000001003
    00000001004
    00000001005
    00000001006
    00000001007
    00000001008
    00000001009
    00000001010

If you need to use such a series in select with other columns of a table, you better use rownum or row_number as suggested by Gordon.

You asked,

Then when is the best way to use the sequence number using the "sequence" function.

This is from Oracle documentation.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back.

After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

So, hope it is clear.If you are simply inserting records into CSV file from Db tables and this code is written in cursor, there is no need to go with the sequence. But then again it depends on how you are fetching from the cursor which you are talking about. If you can give more details, we may help you in constructing the required query/ PL/SQL blocks if any.

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The best way to do this in Oracle is to use rownum or row_number():

select lpad(1000 + rownum, 11, 0) as newcol, . . .
from t;

There is no need for a sequence or for a variable in the cursor either.

You might consider alternatives to using a cursor as well.

Upvotes: 3

Related Questions