Create sequence <seq_name>

[RAVINDRA NARAGA]

Sql>create sequence s1 increment by 1 start with 1;
sql>select s1.nextval from dual;
ans:
Nextval
--------
1
but,how to get below output
nextval
---------
2
3
4
5
6
7
8
9
10
11
12
13
14
15
14 rows

[amitpatel66]

You can do something like this:

[code=oracle]
SQL> create sequence my_test_seq
2 start with 1
3 increment by 1
4 order
5 nocache
6 nocycle;

Sequence created.

SQL> create or replace type my_Tab_num IS TABLE OF NUMBER;
2 /

Type created.


SQL> create or replace function my_Test_func (get_num IN NUMBER) RETURN my_tab_num AS
2 mytab my_Tab_num := my_tab_num();
3 BEGIN
4 FOR I IN 1..get_num LOOP
5 mytab.extend;
6 select my_test_seq.nex tval
7 INTO mytab(I)
8 FROM dual;
9 END LOOP;
10 RETURN mytab;
11 END;
12 /

Function created.

SQL> select my_test_Seq.nex tval from dual;

NEXTVAL
----------
56

SQL> SELECT * FROM TABLE(my_Test_f unc(3));

COLUMN_VALUE
------------
57
58
59

SQL> SELECT * FROM TABLE(my_Test_f unc(15));

COLUMN_VALUE
------------
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

15 rows selected.

SQL> select my_test_seq.nex tval from dual;

NEXTVAL
----------
75

SQL> SELECT * FROM TABLE(my_Test_f unc(2));

COLUMN_VALUE
------------
76
77

SQL>

[/code]