1 2 Previous Next 17 Replies Latest reply: Jan 29, 2009 8:10 AM by user601042 Go to original post RSS
      • 15. Re: random  number generation
        William Robertson
        Why wouldn't you just attempt the INSERT using something like
        insert into test (no) values(DBMS_RANDOM.VALUE(nmin,nmax ));
        then handle any <tt>DUP_VAL_ON_INDEX</tt> exception if one occurs?

        btw PL/SQL does provide LOOP constructions.
        • 16. Re: random  number generation
          312406
          Simple way is to concatenate the random value with a sequence no.

          select seqname.nextvalue || dbms_random ....
          from dual.

          This way you need not worry about the uniqueness as the seq no will ensure that and also you get a random number.

          Make sure your number field is sufficient enough in size.

          For ex: if you give a seq no of size say 2 digit then the unique ness will be guaranteed for 100 numbers only.
          • 17. Re: random  number generation
            user601042
            Hi,
            For our case, in Distribution Centers label id should be unique. So as one guy stated we use sequence + random number + check digit.

            calcualting check digit:

                 FOR l_pos IN 1 .. l_len LOOP

                 IF (MOD(l_pos+1,2) = 0) THEN

                 l_even_sum := l_even_sum + TO_NUMBER(SUBSTR('OUR STRING',l_last_char_pos-l_pos,1));

            ELSE

                 l_odd_sum := l_odd_sum + TO_NUMBER(SUBSTR('OUR STRING',l_last_char_pos-l_pos,1));

                 END IF;

                 END LOOP;

                 l_total_sum := (3 * l_even_sum ) + l_odd_sum ;

                 l_n_check_digit := MOD((10 - MOD(l_total_sum,10)),10);

            finally we will append this check digit with that.

            We know volume of data. based on that we decided purge criteria.So always unique id will be there.
            1 2 Previous Next