1 2 Previous Next 17 Replies Latest reply: Jun 26, 2012 3:01 PM by Etbin Go to original post RSS
      • 15. Re: generate 5 digit unique random number based on year
        padders
        CREATE GLOBAL TEMPORARY NOGAP SEQUENCE sequence_name UNIQUE RANDOM_ORDER CYCLE ANNUALLY
        Personally I think Oracle should just pull its finger out and start implementing these things.
        • 16. Re: generate 5 digit unique random number based on year
          Nicosa-Oracle
          Hi,

          One way could be to use dbms_random package, and just check for existence before insert.

          Let's say I inserting to table T, which also store the date of insertion (dt). I generate several yuid (YearlyUniqueIDentifier), but only insert the first one that doesn't already exists within the current year :
          Scott@my11g SQL>create table t(
            2  yuid varchar2(5)
            3  ,dt date
            4  ,val varchar2(10)
            5  );
          
          Table created.
          
          Scott@my11g SQL>insert into t select to_char(level-1,'fm00000'),to_date('2011','yyyy'), 'foo' from dual connect by level <= 100000;
          
          100000 rows created.
          
          Scott@my11g SQL>
          Scott@my11g SQL>insert into t select to_char(2*level,'fm00000'),sysdate, 'blabla' from dual connect by level <50000;
          
          49999 rows created.
          The table is already "half-filled" with all even yuid values that can fit in it for the current year.
          The table is filled with all possible yuid for previous year.

          The insert is done as follows, generating 10 random values, and only inserting the fist one that doesn't exists with current year.
          Scott@my11g SQL>ed
          Wrote file afiedt.buf
          
            1  insert into t
            2  select u, sysdate, 'Hello !'
            3  from (
            4          select u
            5          from (
            6                  select to_char(trunc(dbms_random.value(1,100000)),'fm00000') u
            7                  from dual
            8                  connect by level<=10
            9          ) v
           10          where not exists (
           11                  select null
           12                  from t
           13                  where t.yuid=v.u
           14                  and t.dt between trunc(sysdate,'year') and add_months(trunc(sysdate,'year'),12))
           15  )
           16* where rownum <= 1
          Scott@my11g SQL>/
          
          1 row created.
          
          Scott@my11g SQL>/
          
          1 row created.
          
          Scott@my11g SQL>/
          
          1 row created.
          
          Scott@my11g SQL>/
          
          1 row created.
          
          Scott@my11g SQL>
          As long as the not exists is fast, the overall insert should be fast.
          You can modify the number of generated rows so that it doesn't "fail" too often.

          But this doesn't address the multiuser issue that a sequence would.
          • 17. Re: generate 5 digit unique random number based on year
            Etbin
            If just five digits numbers are needed you could extract them from some injective (http://en.wikipedia.org/wiki/Injective_function) transformation and you could easily verify those for uniqueness:
            substr(to_char(ln(sequence_value)),10,5)
            substr(to_char(exp(1 / sequence_value)),20,5)
            ...

            Regards

            Etbin
            1 2 Previous Next