Friday, August 05, 2011

Reset 'Next Value' of an Oracle sequence

In all RDBMS's, the power of sequences is just priceless. This is also true for Oracle...Sometime we happen to need to reset the next value of a sequence and unfortunately there is no single line command to do it in Oracle. This article show the basic steps to reset the next or current values of an Oracle sequence.

Let's say that we have a sequence 'my_sequence' (pretty original huh?) whose nextval is 900000:

select my_sequence.nextval from dual;

> 900000

However we discover that after deleting some data from a table, we realize that we need to reset the sequence so that the next value is 400000. This can be done in 3 steps:
  1. Increment the sequence by a negative number which is the difference between the current value and the new value (900001 - 400000)
  2. Get the next value from dual
  3. Re-adjust the increment of the sequence back to 1.

In SQL this can be done as follows:

alter sequence my_sequence increment by -500001;
select my_sequence.nextval from dual;
alter sequence my_sequence increment by 1;

Tuesday, August 02, 2011

SQL UPDATE with JOIN statements in Oracle

You are reading this article as you probably have not found an easy way to UPDATE tables which are in a JOIN statement, in an Oracle database. The problem's solution may sound trivial, until you try it out yourself!

Without going through the erroneous ways to try to update joined tables, let me tell you that the only way you can reliably achieve this in Oracle is by using PL/SQL. The reason is that Oracle needs to know beforehand the joined dataset which is to be updated. Thus this needs to be stored in a cursor, and then updated accordingly.

The following piece of PL/SQL code depicts two tables - 'target' and 'source'. We need to update 'target' rows which are found in 'source' by the corresponding value in 'source'.

DECLARE
   commit_interval number := 1;
   l_count number := 0;    
CURSOR c1 is
    SELECT   
        s.col1 s_col1,
        s.col2 s_col2,
        s.col3 s_col3
    FROM
        target t,
        source s
    WHERE
        t.col1 = s.col1 and
        t.col2 = s.col2 and
        t.col3 = s.col3;
BEGIN
    FOR r IN c1 LOOP
       UPDATE target t
          SET t.col1 = r.s_col1
        WHERE
                t.col2 = r.s_col2 and
                t.col3 = r.s_col3;
      l_count := l_count + 1;
      IF (l_count >= commit_interval) THEN
        COMMIT;
        l_count := 0;
      END IF;
   END LOOP
 COMMIT;
END;
It is important to include all columns in the cursor which are referenced by the WHERE clause in the UPDATE statement.