Skip to Main Content
  • Questions
  • Generate Random number and compare it for uniqueness in database

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Nitesh.

Asked: April 29, 2019 - 12:09 pm UTC

Last updated: May 13, 2019 - 3:22 am UTC

Version: 11 g

Viewed 1000+ times

You Asked

Hi Tom,

I have a situation where I have to create a function which will return a unique number (Suffix R and 7 numbers) which will than be compared with data present in database with a particular column which is primary key. if the number generated by function is unique and does not exists in database a separate procedure will insert it into database else it will through an error and than function needs to generate a new value.

I have tried using
'R'||TRUNC(DBMS_RANDOM.VALUE(low => 1, high => 10))||ROUND(DBMS_RANDOM.VALUE(low => 100, high => 9999))

but not able to manage all the conditions.


create or replace FUNCTION FN_GET_NEW_IRD_NO(pSeriesNo IN VARCHAR2) RETURN VARCHAR2

AS
lIrdNo VARCHAR2(50);
lDTH_Irdno VARCHAR2(14);
lIPTV_Irdno VARCHAR2(14);
lReturnVal VARCHAR2(200);
lSmartNoDTH VARCHAR2(15);
lSmartNoIPTV VARCHAR2(15);
lDecVal NUMBER;

BEGIN

IF LENGTH(pSeriesNo) > 5 THEN
RETURN 'IRD Series number should be less or equals to 5.';
END IF;

lIrdNo := 'R'||pSeriesNo||TRUNC(DBMS_RANDOM.VALUE(low => 1, high => 10))||ROUND(DBMS_RANDOM.VALUE(low => 100, high => 9999));

SELECT MAX(ird_no) INTO lDTH_Irdno
FROM sapadmin.eqt_movement_app
WHERE ird_no LIKE 'R'||lIrdNo;

SELECT MAX(ird_no)INTO lIPTV_Irdno
FROM sapadmin.eqt_movement_app
WHERE ird_no = lIrdNo;

DBMS_OUTPUT.PUT_LINE('OK');

IF lDTH_Irdno IS not NULL THEN
SELECT UNIQUE SMART_CARD_NO INTO lSmartNoDTH
FROM sapadmin.eqt_movement_app
WHERE ird_no = lDTH_Irdno AND ROWNUM =1;
ELSE
DBMS_OUTPUT.PUT_LINE('IRD_NO IS NULL');
END IF;

IF lIPTV_Irdno IS not NULL THEN
SELECT UNIQUE SMART_CARD_NO INTO lSmartNoIPTV
FROM sapadmin.eqt_movement_app
WHERE ird_no = lIPTV_Irdno AND ROWNUM =1;
END IF;

SELECT HEX2DEC(lIPTV_Irdno) INTO lDecVal FROM DUAL;
SELECT DEC2HEX(lDecVal +1) INTO lIPTV_Irdno FROM DUAL;
lDTH_Irdno := SUBSTR(lDTH_Irdno, 2, LENGTH(lDTH_Irdno)) +1;
lSmartNoDTH := SUBSTR(lSmartNoDTH, 2, LENGTH(lSmartNoDTH)) +1;
lSmartNoIPTV := SUBSTR(lSmartNoIPTV, 2, LENGTH(lSmartNoIPTV)) +1;

lReturnVal := 'DTH IRD No : '|| lDTH_Irdno ||', IPTV IRD No : '||lIPTV_Irdno ||' DTH Smart No : '
|| lSmartNoDTH ||', IPTV Smart No :'|| lSmartNoIPTV;
RETURN lReturnVal;

END FN_GET_NEW_IRD_NO;


and Connor said...

That's a weird requirement because you'll be out of numbers after 10 million - and I'd encourage you to rethink this approach.

Anyway, since its only 10 million numbers here's a different strategy - we'll precreate them all in a random order, and you just pick one off each as required.

I've done a million below but you get the idea

SQL> create global temporary table t1
  2  on commit preserve rows as
  3  select 'R'||to_char(rownum,'FM0000000') tag
  4  from ( select 1 from dual connect by level <= 1000),
  5       ( select 1 from dual connect by level <= 1000)
  6  where rownum < 1000000
  7  order by dbms_random.value;

Table created.

SQL>
SQL> create table t (
  2    id  int          not null,
  3    tag varchar2(10) not null,
  4    constraint t_pk primary key ( id )
  5  )
  6  organization index;

Table created.

SQL>
SQL> insert into t
  2  select rownum, tag from t1;

999999 rows created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> select /*+ index(t) first_rows(10) */ id, tag
  2  from t
  3  where rownum <= 10 ;

        ID TAG
---------- ----------
         1 R0919471
         2 R0594711
         3 R0403352
         4 R0535468
         5 R0954674
         6 R0585909
         7 R0254793
         8 R0868272
         9 R0046145
        10 R0077835

10 rows selected.

SQL>
SQL> variable my_key varchar2(10)
SQL> declare
  2    rc sys_refcursor;
  3    pk int;
  4  begin
  5    loop
  6      open rc for
  7        select /*+ index(t) first_rows(10) */ id, tag
  8        from t
  9        where rownum <= 5 for update skip locked;
 10
 11      fetch rc into pk, :my_key;
 12      if rc%found then
 13        delete from t where id = pk;
 14        close rc;
 15        exit;
 16      else
 17        close rc;
 18      end if;
 19   end loop;
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL> print my_key

MY_KEY
--------------------------------
R0919471


That session will *own* that key until it commits or rolls back. No-one else will be able to get it.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Need function

Nitesh Sharma, May 05, 2019 - 4:57 pm UTC

HI Connor,

Thanks for the new approach for this requirement.

But here I need function which will return a random value of 7 digits which will be check in the table for a particular column value. If that number is not present it will return the number generated else it will generate a new number.

we can ignore prefix 'R' as if now.

Actually with your suggestion I need to run again and again manually but I need function because that function unique number will be used in many parameterized procedure as input.

Sorry if sounds bit weird but I have tried all what I can do.
Connor McDonald
May 13, 2019 - 3:22 am UTC

But here I need function which will return a random value of 7 digits which will be check in the table for a particular column value. If that number is not present it will return the number generated else it will generate a new number.

Then the initialization code simply changes:

SQL> insert into t
  2  select rownum, tag from t1;


to

SQL> insert into t
  2  select rownum, tag from t1
  3  where tag not in ( select ... from existing_table )


Now you have a list of the all of the unused tags. From that point on, you follow the approach I listed and you never need to "check" again

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.