SOLVED

ORA-08002: sequence string.CURRVAL is not yet defined in this session

Asked by OracleDba••13 views•oracle

#oracle#error

Solutions(1)

Accepted Solution
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
ORA-08002: sequence string.CURRVAL is not yet defined in this session

Cause:

sequence CURRVAL has been selected before sequence NEXTVAL

Action:

select NEXTVAL from the sequence before selecting CURRVAL

ORA-08002 occurs when you try to get CURRVAL of a sequence, before requesting its NEXTVAL in the session. ORA-08002 can be reproduced as following:

SQL> create sequence myseq start with 1 increment by 1 nocycle;

Sequence created.

SQL> select myseq.currval from dual;

select myseq.currval from dual

       *

ERROR at line 1:

ORA-08002: sequence MYSEQ.CURRVAL is not yet defined in this session

Explaination of ORA-08002: As per definition CURRVAL returns sequence last value requested by the "current session". ORA-08002 here means that the current session never issued MYSEQ.NEXTVAL, so there is no last value of the sequence in the session.

The exception of ORA-08002 can not be explained completely without explaining 2 main aspects of sequences

1) NEXTVAL, CURRVAL and SESSION

2) user_sequences.last_number and sequence cache

1) NEXTVAL and CURRVAL and SESSION

To understand NEXTVAL and CURRVAL of sequences and SESSION, lets play with a sequence in two session. The steps we are going to follow here is

a) Create a sequence

b) In Session 1 generate sequence.nextval

c) In Session 2 generate sequence.nextval 1000 times

d) Check the sequence.CURRVAL in session one.

SESSION 1:

SQL> create sequence myseq start with 1 increment by 1 nocycle

  Sequence created.

  SQL> select myseq.nextval from dual;

     NEXTVAL

  ----------

           1

SESSION 2:

SQL> declare x number;

    2  begin

    3  for i in 1..1000 loop

    4  select myseq.nextval into x from dual;

    5  end loop;

    6  end;

    7  /

  PL/SQL procedure successfully completed.

  SQL> select myseq.currval from dual;

     CURRVAL

  ----------

        1001

SESSION 1:

SQL> select myseq.currval from dual;

     CURRVAL

  ----------

           1

So it became obviously, that the 'currval' is stored in the session's pga/uga memory and not in the data dictionary. That is the reason that in Session 1 myseq.currval is still 1 however in session 2 myseq was moved 1000 times.

2) USER_SEQUENCES.LAST_NUMBER AND SEQUENCE CACHE

We can use user_sequences.last_number to get what nextval would have returned, only if the sequence was created with NOCACHE. As in following example NEXTVAL is 1001 but user_sequences.LAST_NUMBER is 1021 because the sequence was not created with NOCACHE option. The value in user_sequences.last_number jumps by the cache size and is usually not what is for NEXTVAL.

SQL> create sequence myseq start with 1 increment by 1 nocycle;

  Sequence created.

  SQL> declare x number;

    2  begin

    3  for i in 1..1000 loop

    4  select myseq.nextval into x from dual;

    5  end loop;

    6  end;

    7  /

  PL/SQL procedure successfully completed.

  SQL> select myseq.nextval from dual;

     NEXTVAL

  ----------

        1001

  SQL> select  last_number

    2  from  user_sequences

    3  where sequence_name = 'MYSEQ';

  LAST_NUMBER

  -----------

         1021

Lets try the same example of above with the sequence with NOCACHE.

SQL>  drop sequence myseq;

  Sequence dropped.

  SQL> create sequence myseq start with 1 increment by 1 nocache nocycle;

  Sequence created.

  SQL> declare x number;

    2  begin

    3  for i in 1..1000 loop

    4  select myseq.nextval into x from dual;

    5  end loop;

    6  end;

    7  /

  PL/SQL procedure successfully completed.

  SQL> select myseq.nextval from dual;

     NEXTVAL

  ----------

        1001

  SQL> select  last_number

    2  from  user_sequences

    3  where sequence_name = 'MYSEQ';

  LAST_NUMBER

  -----------

         1002

So as the sequence was created with NOCACHE option, user_sequences.last_number is returning what nextval would have returned.

Hope you have enjoyed is article and it was beneficial for you. 🙂
OracleDba•

Post Your Solution