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);
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.
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.
