Assign number value circularly in PL/SQL

[tmf33uk]

Hi!

I have a table with an id column which I would like to update with values 1 to n. Value n is to be defined each time the script is run.

I´m using the MOD function, but it´s not working as desired. If I set NUMBER_SCRIPTS=3 most of the rows are assigned ID=3. Should be assigned in the order of size by bytes (see cursor) as 1,2,3,1,2,3,1,2,3,etc. and if I set define NUMBER_SCRIPTS to 5, for example, the rows in descending order by size should be assigned 1,2,3,4,5,1,2,3,4,5,1,2,etc.

Please let me know if the objective is not clear and I´ll explain with more "real" examples.

Thanks in advance for your help. I have found this forum to be most valuable in the past and I am most grateful for it.

The relevant part of the script is as follows:

DEFINE NUMBER_SCRIPTS=&1
DECLARE
N_SCRIPT INTEGER:=&&NUMBER_SCRIPTS;

CURSOR CONTROL IS
SELECT SEGMENT_NAME, SEGMENT_TYPE, ROWNUM
FROM REPLICA_CONTROL_TABLE
WHERE SEGMENT_TYPE ='MVIEW'
AND ACCION='REFRESH'
ORDER BY BYTES DESC;

FOR R_CONTROL IN CONTROL LOOP
UPDATE TABLE SET ID=MOD(ROWNUM, N_SCRIPT)+1;
commit;
END LOOP;

Regards,
Teri.

 

[BJCooperIT]

One often misunderstood Oracle concept is the use of ROWNUM. The example below shows ROWNUM, labeled “RN” on an unsorted query.

NUM_LOGINS DEVELOPER          RN
---------- ---------- ----------
        43 WHITE               1
        27 BLACK               2
        15 PINK                3
       102 RED                 4
        78 GREEN               5
        56 BLUE                6

6 rows selected.

SQL>

The ROWNUM number is assigned before an ORDER BY is performed. In the example below, notice that Red, despite being the first row of this ordered query, is still ROWNUM 4.

NUM_LOGINS DEVELOPER          RN
---------- ---------- ----------
       102 RED                 4
        78 GREEN               5
        56 BLUE                6
        43 WHITE               1
        27 BLACK               2
        15 PINK                3

6 rows selected.

SQL>

Often developers want to select only a few rows or a min/max row by referencing the ROWNUM. Suppose you wanted to find the three developers who have logged in the most (RED, GREEN and BLUE).

The following query does not work because the ROWNUM is assigned prior to arranging the rows in order:

SQL> select tt.*, ROWNUM rn
  2   from   test_tab tt
  3  where   rownum < 4
  4  order by num_logins DESC;

NUM_LOGINS DEVELOPER          RN
---------- ---------- ----------
        43 WHITE               1
        27 BLACK               2
        15 PINK                3

SQL>

In an ordered query, you may use an inline view to use the ROWNUM in the WHERE clause:

SQL> select ilv.*, rownum new_rn
  2  from   (select tt.*, rownum rn
  3          from   test_tab tt
  4          order by num_logins desc) ilv
  5  where  rownum < 4;

NUM_LOGINS DEVELOPER          RN     NEW_RN
---------- ---------- ---------- ----------
       102 RED                 4          1
        78 GREEN               5          2
        56 BLUE                6          3

SQL>

Here you are referencing the ROWNUM assigned to the inline view and can restrict the number of rows returned.

In the same way, you can use an inline view to achieve what you are looking for:

CURSOR CONTROL IS
    SELECT  ilv.*, ROWNUM
    FROM    (   SELECT  SEGMENT_NAME, SEGMENT_TYPE
                FROM    REPLICA_CONTROL_TABLE
                WHERE   SEGMENT_TYPE ='MVIEW'
                AND     ACCION='REFRESH'
                ORDER BY BYTES DESC
             ) ilv
   ;

FYI - next time it would be better to post this in one of the Oracle forums (8i, 9i, 10g) for a quick response.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~ George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)

 

[tmf33uk]

Thanks George! Point learned. )

However, I´ve ran my script with a NUMBER_SCRIPTS=5 and all rows were assigned a value of 5.

I am wondering whether having made the inline view change you suggested, the MOD(ROWNUM, N_SCRIPT)+1 still needs changing.

After all, dividing some rownums in the table by N_SCRIPT gives decimal values which round up to the same as the next rownum calculation value.

Any ideas?

T.
Ps. I´ll post this in the Oracle (8i, 9i, 10g) forums also as you suggested. )

 

[BJCooperIT]

Does this help?

SQL> SELECT ilv.*, ROWNUM new_rn,
  2         decode(MOD(ROWNUM,3),0,3,MOD(ROWNUM,3)) calc
  3  FROM   (SELECT tt.*,
  4                 ROWNUM rn
  5          FROM   test_tab tt
  6          ORDER  BY num_logins DESC) ilv
  7  ;

NUM_LOGINS DEVELOPER          RN     NEW_RN       CALC
---------- ---------- ---------- ---------- ----------
       102 RED                 4          1          1
        78 GREEN               5          2          2
        56 BLUE                6          3          3
        43 WHITE               1          4          1
        27 BLACK               2          5          2
        15 PINK                3          6          3

6 rows selected.

SQL>

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~ George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)

 

[carp]

If you are using a cursor, why are you using ROWNUM at all?

DEFINE NUMBER_SCRIPTS=&1
DECLARE
N_SCRIPT INTEGER:=&&NUMBER_SCRIPTS;

CURSOR CONTROL IS
SELECT SEGMENT_NAME, SEGMENT_TYPE 
FROM REPLICA_CONTROL_TABLE
WHERE SEGMENT_TYPE ='MVIEW'
AND ACCION='REFRESH'
ORDER BY BYTES DESC;

FOR R_CONTROL IN CONTROL LOOP
UPDATE TABLE SET ID=MOD(CONTROL%ROWCOUNT, N_SCRIPT)+1;
END LOOP;
commit;

Also note that I moved the COMMIT outside of your cursor loop. Unless you have a pressing reason to commit after every row, your performance will be much better if you wait until all of the updates are done to do your commit.