Odtnhj

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;