| Increment sequence to max value of column [message #614323] |
Tue, 20 May 2014 17:27  |
 |
rose@ora
Messages: 3
Registered: May 2014
|
Junior Member |
|
|
Hi,
I am trying to learn PL/sql and writing a query to increment the sequence to the max value of column .The code is working fine but it is incrementing to a value which is 20 greater than the max(column_value) of a table.Pls let me know where I am doing wrong and also if any best practices which I can incorporate .
DECLARE
v_SEQ_VAL NUMBER(10);
v_PK_MAX_VAL NUMBER(10);
v_TEMP NUMBER(10);
BEGIN
SELECT MAX(COLUMN_ID) INTO v_PK_MAX_VAL FROM TABLE;
SELECT seq.CURRVAL INTO v_SEQ_VAL FROM DUAL ;
WHILE v_SEQ_VAL <= v_PK_MAX_VAL
LOOP
SELECT seq.NEXTVAL FROM INTO v_TEMP DUAL;
v_SEQ_VAL := v_SEQ_VAL +1;
END LOOP ;
END;
pls note: I have been able to the above through sql, but I want to do it through pl/sql.It is solely for my learning purpose
Thanks in advance
[Updated on: Tue, 20 May 2014 17:39] Report message to a moderator |
|
|
|
| Re: Increment sequence to max value of column [message #614339 is a reply to message #614323] |
Wed, 21 May 2014 00:16  |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Quote:
... it is incrementing to a value which is 20 greater ...
That would be, I suspect, because of the default CACHE size (which is 20). CREATE SEQUENCE says
Quote:
If you omit both CACHE and NOCACHE, then the database caches 20 sequence numbers by default.
P.S. By the way, loop is potentially not the best way to increment a sequence number because your MAX(COLUMN_ID) might be a very, very large number so you might have an impression that your code entered an infinite loop (while it is just fetching the sequence number again, and again, and again ...).
ALTER SEQUENCE is, therefore, a better option. Have a look at this (SQL) example:
SQL> select seq_brisime.nextval from dual;
NEXTVAL
----------
1
SQL> alter sequence seq_Brisime increment by 1000;
Sequence altered.
SQL> select seq_brisime.nextval from dual;
NEXTVAL
----------
1001
SQL> alter sequence seq_brisime increment by 1;
Sequence altered.
SQL> select seq_brisime.nextval from dual;
NEXTVAL
----------
1002
SQL>
As you are practicing PL/SQL, try to convert it to PL/SQL. Hint: you'll need dynamic SQL (EXECUTE IMMEDIATE) in order to issue ALTER SEQUENCE command.
[Updated on: Wed, 21 May 2014 00:22] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
