CREATE OR REPLACE PROCEDURE CREATE_PERSON_SP (
    p_person_ID         VM_PERSON.PERSON_ID%TYPE,
    p_person_email      VM_PERSON.PERSON_EMAIL%TYPE,
    P_person_given_name VM_PERSON.PERSON_GIVEN_NAME%TYPE,
    p_person_surname    VM_PERSON.PERSON_SURNAME%TYPE,
    p_person_phone      VM_PERSON.PERSON_PHONE%TYPE
)
IS
ex_error exception;
p_count number (10);
err_msg_txt varchar(100) :=null;

BEGIN

select count (*)
into p_count
from VM_PERSON
where p_person_email = PERSON_EMAIL;

if p_count > 0
then
err_msg_txt := 'The email already exists, try another one ';
raise ex_error;
elsif p_person_email is null then
err_msg_txt := 'the email  must not be null.';
raise ex_error;
end if;

    INSERT INTO VM_PERSON ("PERSON_ID", "PERSON_EMAIL", "PERSON_GIVEN_NAME", "PERSON_SURNAME", "PERSON_PHONE")
    VALUES (p_person_id, p_person_email, p_person_given_name, p_person_surname, p_person_phone);
    COMMIT;

Exception
when ex_error then
dbms_output.put_line(err_msg_txt);
rollback;
when others then
dbms_output.put_line(' the error code is: ' || sqlcode);
dbms_output.put_line(' the error msg is: ' || sqlerrm);
rollback;

END;
/
BEGIN
    CREATE_PERSON_SP(6,'OLE@MORTEN.org','Ali','Amin','+47 91919191');
END;
/