Hello,
I am having trouble with generating email addresses from two columns: firstname and lastname. No duplicate emails. I need to use function and exceptions... However my code does not work as I would like it to. It does not generate different email if such email already exists.
Initially program needs to take first letter from the firstname and 7 letters from the last name, however IF someone has the same initials then the program takes two letters from first name and 6 letters from the lastname.
The final result should be something like below: table name emails
ID EESNIMI PERENIMI EMAIL
---------- --------------- --------------- -------------------------
11 Anu Asser anasser@it.ee
1 Anneli Asser aasser@it.ee
2 Madis Sepp msepp@it.ee
3 Jelena Asser jasser@it.ee
4 Pippi Pikksukk ppikksuk@it.ee
5 Castillo Grand cgrand@it.ee
6 Uku Ukakas uukakas@it.ee
7 Mari Maasikas mmaasika@it.ee
8 Viki Viirik vviirik@it.ee
9 Peeter Petski ppetski@it.ee
10 Jakob Son json@it.ee
The important part in the above table is first two emails. The code that I currently have generates same email address for both of them rows and its "aasser@it.ee" but I need them to be different.
However in the beginning the email column is empty aka email = NULL
So my function for generating email is as follows:
create or replace function create_email (firstname in varchar2, lastname in varchar2, n in number, m in number)
return varchar2
is
begin
return translate(lower(substr(firstname,0,n))||''||lower(substr(lastname,0,m)), 'õüöäšž', 'ouoasz')||'@it.ee';
end create_email;
.
/
And for updating the table with the right emails is script as follows: Where I want to use the above function to generate the email.
DECLARE
rec_firstname emails.firstname%TYPE;
rec_lastname emails.lastname%TYPE;
CURSOR email_cursor IS
SELECT firstname, lastname
FROM emails
WHERE email IS NULL;
BEGIN
OPEN email_cursor;
LOOP
FETCH email_cursor INTO rec_firstname, rec_lastname;
EXIT WHEN email_cursor%NOTFOUND;
BEGIN
UPDATE emails SET email = create_email(rec_firstname, rec_lastname, 1, 7)
WHERE firstname = rec_firstname AND lastname = rec_lastname;
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE emails SET email = create_email(rec_firstname, rec_lastname, 2, 6)
WHERE firstname = rec_firstname AND lastname = rec_lastname;
COMMIT;
END;
END LOOP;
CLOSE email_cursor;
END;
.
/
So something is wrong or incorrect and I do not get what. Can some one spot the mistake and help me make it work?
Thank you
*Edit I apologize for not specifying that it is an assignment. I did provide all the necessary information in my opinion. I only left you the part where you have to clean the names of off "special characters" i.e ä > a etc.
Table of emails is provided in all its glory.