Vfrdtyky

I would like to use Oracle SQL to generate unique sAMAccountName for users. The logic to generate the unique name is the First letter of first name + entire Last Name.

If the combination already exists, append 1 at the end of the unique name.
If the second combination already exists, increment till uniqueness is achieved.

For example, 5 users named "Tom Kelly" and 3 users named "Tom Ke" will have the following unique names:

1. TKelly


2. TKelly1


3. TKelly2


4. TKelly3


5. TKelly4


6. TKe


7. TKe1


8. TKe2

I have written a procedure for this but it's not working completely. I think the logic needs to be updated in this query of the procedure:

SELECT SUBSTR(Custom_Unique_Name,-1,1) INTO lastletter 

FROM (SELECT * FROM (SELECT Custom_Unique_Name FROM 

P_USERS WHERE Custom_Unique_Name= item.tempid2 AND 

user_id <> item.user_id ORDER BY Custom_Unique_Name 

DESC) WHERE ROWNUM<=1);

The read-only table P_USERS contains all the users with a primary key as "user_id" and the unique name is stored in the attribute "Custom_Unique_Name"

After calculating the unique name, the attribute "PRIMARY_UNIQUE_NAME" in the master user table "T_MASTER_USERS" needs to be updated. This table's primary key is also "user_id" which holds the same value as in the read-only table "P_USERS"

   DECLARE

    lastletter varchar2(10);

    anyexists varchar2(10);

    lastfive varchar2(10);

    CURSOR c_length IS SELECT SUBSTR(first_name,1,1)||(last_name) as 

    tempid2,LENGTH(last_name) as lengthln, user_id, Custom_Unique_Name 

    FROM P_USERS;

    status varchar2(10);

    BEGIN

     FOR item in c_length

      LOOP

        EXIT WHEN c_length%notfound;

        lastletter:=0;

        SELECT NVL(Custom_Unique_Name,0) INTO status FROM P_USERS 

        WHERE USER_ID=item.user_id; 

        IF status <> '0' THEN /* Checks if the person already has a 

        unique id */

         NULL;   /* If yes, then do nothing and exit the program */

        ELSE

            SELECT COUNT(*) INTO anyexists FROM (SELECT 

            Custom_Unique_Name FROM P_USERS 

            WHERE Custom_Unique_Name = item.tempid2 AND user_id <> 

            item.user_id);

            IF anyexists=0 THEN     /* Check if unique id exists for 

            the user. If not, then assign unique_id to the user */

                UPDATE T_MASTER_USERS SET 

                PRIMARY_UNIQUE_NAME=item.tempid2 WHERE 

                user_id=item.user_id;

                ELSE

                 SELECT SUBSTR(Custom_Unique_Name,-1,1) INTO lastletter 

                 FROM (SELECT * FROM (SELECT Custom_Unique_Name FROM 

                 P_USERS WHERE Custom_Unique_Name= item.tempid2 AND 

                 user_id <> item.user_id ORDER BY Custom_Unique_Name 

                 DESC) WHERE ROWNUM<=1);

                 IF LENGTH(TRIM(TRANSLATE(lastletter, ' 

                 +-.0123456789',' '))) > 0 THEN /* Checks if the count 

                 of equivalent unique names if a number. If not, append 

                 1*/

                    UPDATE T_MASTER_USERS SET 

                    PRIMARY_UNIQUE_NAME=item.tempid2||'1' WHERE 

                    user_id=item.user_id;

                    ELSE

                     lastletter:=lastletter+1;

                     UPDATE T_MASTER_USERS SET 

                     PRIMARY_UNIQUE_NAME=item.tempid2||lastletter WHERE 

                     user_id=item.user_id;

                  END IF;

                END IF;

            END IF;

        END LOOP;

      END;

How about something like this?

SQL> create table users

  2    (username varchar2(20) primary key);



Table created.



SQL> create or replace procedure p_un (par_first_name in varchar2,

  2                                    par_last_name  in varchar2)

  3  is

  4    l_username users.username%type;

  5    l_letter   users.username%type;

  6    l_digit    users.username%type;

  7    retval     users.username%type;

  8  begin

  9    l_username := upper(substr(par_first_name, 1, 1) || par_last_name);

 10

 11    select max(regexp_substr(u.username, '^w+')) l_letter,

 12           max(regexp_substr(u.username, 'd+$')) l_digit

 13      into l_letter,

 14           l_digit

 15      from users u

 16      where u.username like l_username ||'%'

 17        and (   substr(u.username, length(l_username) + 1, 1) between '1' and '9'

 18             or (    regexp_substr(u.username, 'd$') is null

 19                 and substr(u.username, length(l_username) + 1, 1) is null

 20                )

 21            )

 22        and (   to_number(regexp_substr(username, 'd$')) =

 23                 (select max(to_number(regexp_substr(u1.username, 'd$')))

 24                  from users u1

 25                  where u1.username like l_username ||'%'

 26                  and (   substr(u1.username, length(l_username) + 1, 1) between '1' and '9'

 27                       or (    regexp_substr(u1.username, 'd$') is null

 28                           and substr(u1.username, length(l_username) + 1, 1) is null

 29                          )

 30                      )

 31                 )

 32              or regexp_substr(u.username, 'd$') is null

 33             );

 34

 35    if l_letter is null then

 36       retval := upper(l_username);

 37    else

 38       retval := upper(l_username || to_char(to_number(nvl(l_digit, 0)) + 1));

 39    end if;

 40

 41    dbms_output.put_line('l_username = ' || l_username||', letter = '|| l_letter||

 42                         ', digit = '||l_digit ||', new username = ' || retval);

 43    insert into users (username) values (retval);

 44  end;

 45  /



Procedure created.

What does it do?

SELECT (at line 11) searches for existing usernames that

• look like a new username (which is composed of the first letter of the first name and the whole last name) (line 16)


• contains a number at the end (line 17)


• or there's no number at all (lines 18, 19)


• but, if there is a number there, select MAX number (line 23)


• if such a username doesn't exist, return its default value (line 36)


• if it exists, add 1 to the MAX number and attach it to the default username value (line 38)

Testing:

SQL> exec p_un('Tom', 'Kelly');

l_username = TKELLY, letter = , digit = , new username = TKELLY



PL/SQL procedure successfully completed.



SQL> exec p_un('Tom', 'Kelly');

l_username = TKELLY, letter = TKELLY, digit = , new username = TKELLY1



PL/SQL procedure successfully completed.



SQL> exec p_un('Tom', 'Kelly');

l_username = TKELLY, letter = TKELLY1, digit = 1, new username = TKELLY2



PL/SQL procedure successfully completed.



SQL> exec p_un('Tom', 'Ke');

l_username = TKE, letter = , digit = , new username = TKE



PL/SQL procedure successfully completed.



SQL> exec p_un('Tom', 'Ke');

l_username = TKE, letter = TKE, digit = , new username = TKE1



PL/SQL procedure successfully completed.



SQL> exec p_un('Tom', 'Kelly');

l_username = TKELLY, letter = TKELLY2, digit = 2, new username = TKELLY3



PL/SQL procedure successfully completed.



SQL> exec p_un('Tom', 'Ke');

l_username = TKE, letter = TKE1, digit = 1, new username = TKE2



PL/SQL procedure successfully completed.



SQL> exec p_un('Tom', 'Ke');

l_username = TKE, letter = TKE2, digit = 2, new username = TKE3



PL/SQL procedure successfully completed.

Result:

SQL> select * From users;



USERNAME

--------------------

TKE

TKE1

TKE2

TKE3

TKELLY

TKELLY1

TKELLY2

TKELLY3



8 rows selected.

Try below Anonymous block for your scenario:

  create table P_USERS

     (first_name varchar2(20),

     last_name   varchar2(20),

     user_id   number);



  insert into P_USERS values ('Tom','Kelly',1);

 insert into P_USERS values ('Tom','Kelly',2);

 insert into P_USERS values ('Tom','Kelly',3);

 insert into P_USERS values ('Tom','Kelly',4);

 insert into P_USERS values ('Tom','Kelly',5);

 insert into P_USERS values ('Tom','Ke',6);

 insert into P_USERS values ('Tom','Ke',7);

 insert into P_USERS values ('Tom','Ke',8);

 commit;



 create table T_MASTER_USERS

 ( user_id   number,

 PRIMARY_UNIQUE_NAME varchar2(20));



  insert into T_MASTER_USERS values (1,NULL);

 insert into T_MASTER_USERS values (2,NULL);

 insert into T_MASTER_USERS values (3,NULL);

 insert into T_MASTER_USERS values (4,NULL);

 insert into T_MASTER_USERS values (5,NULL);

 insert into T_MASTER_USERS values (6,NULL);

 insert into T_MASTER_USERS values (7,NULL);

 insert into T_MASTER_USERS values (8,NULL);

 commit;





 DECLARE

    cursor uniq_grp is SELECT SUBSTR(first_name,1,1)||(last_name) as 

    tempid2 ,count(*) as CNT

    FROM P_USERS

    group by SUBSTR(first_name,1,1)||(last_name);



    cursor concat_names is SELECT SUBSTR(first_name,1,1)||(last_name) as 

    tempid2, user_id 

    FROM P_USERS;



    V_CNT number;



 BEGIN



    FOR C1 in uniq_grp



    LOOP



    FOR C2 IN concat_names

    LOOP

    IF C2.tempid2=C1.tempid2 THEN 

     V_CNT := c1.CNT -1;

    UPDATE T_MASTER_USERS SET 

      PRIMARY_UNIQUE_NAME=c2.tempid2||V_CNT WHERE 

       user_id=C2.user_id;

       c1.cnt := V_CNT;

    END IF;

    END LOOP;



    COMMIT;



    END LOOP;



END;

create or replace procedure generate_unique(par_first_name in varchar2,par_last_name in varchar2)
(par_first_name in varchar2,par_last_name in varchar2)

is

l_username users.username%type;

l_letter users.username%type;

begin

 l_username := upper(substr(par_first_name, 1, 1) || par_last_name); 



 dbms_output.put_line('l_username = ' || l_username );  

SELECT CASE WHEN MAX(REGEXP_REPLACE(UPPER(USERNAME),'[^0-9]', '')) IS NULL

            THEN l_username 



            ELSE l_username 



                    || 



                    (MAX(REGEXP_REPLACE(UPPER(USERNAME),'[^0-9]', ''))+1) 



    END into l_letter FROM    USERS WHERE   REGEXP_LIKE(UPPER(USERNAME),l_username||'[0-9]*$'); 

dbms_output.put_line('unique userid is ' || l_letter);

end;