oracle sql cursor update an email domain name using an email domain name

Question

I want to give a domain name to change to a new domain name. the procedure will pass two string. not using a Primary key but using the Email execute procedure('gmail.com', 'hotmail.com');

all the emails with 'Gmail.com' in the database will change the new domain name with 'Hotmail.com'. and here is my code.

I have an error with 'wrong number or type of arguments in call to PR_Q3'

create procedure PR_Q3
is P_NewEamil varchar2(50); P_OldEmail varchar2(50);
cursor E_info is select Email_Address from Broker where P_OldEmail = Email_Address
for update of Email_Address;
begin 
open E_info;
fetch E_info into P_NewEamil;
while E_info%found loop 
if(P_NewEamil like '%.com') then 
update Broker set Email_Address = P_NewEamil where Email_Address= P_OldEmail;
else 

 end if;
end loop;
close E_info;

 end PR_Q3;

Answer 1

Your procedure should look like this:

create procedure PR_Q3(p_oldemail in varchar2,
                       p_newemail in varchar2)
As
Begin

UPDATE BROKER
SET
    EMAIL_ADDRESS = REPLACE(EMAIL_ADDRESS, p_oldemail, p_newemail)
WHERE
    REGEXP_LIKE ( EMAIL_ADDRESS,'.*@'|| p_oldemail|| '$' );

Commit;

End;
/

To call this procedure, you need to pass both the domain as following:

Begin
Pr_q3('gmail.com', 'hotmail.com');
End;
/

Cheers!!