checking domain of email in PLSQL

Question

I am trying to check the domain part of an email before a user is allowed to register.

`#OWNER#.REGISTER_CUSTOMER(
IN_USERNAME => :P102__USERNAME,
IN_PASSWORD => :P102__PASSWORD,
IN_FORENAME => :P102__FORENAME,
IF IN_EMAIL LIKE '@% study.beds.ac.uk'  THEN
{IN_EMAIL => :P102__EMAIL,}
END IF;
IN_TEL => :P102__TEL,
IN_SURNAME => :P102__SURNAME);`

Unfortunately, it does not appear to be working and gives me this error code.

ora_sqlerrm: ORA-06550: line 5, column 1: PLS-00103: Encountered the symbol "IF" when expecting one of the following: ( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

Much thanks in advice for your help.

Answer 1

I don't know what test you are trying to apply, but it looks like you need a case statement. This would be along the lines of:

register_customer(
    in_username => :p102__username,
    in_password => :p102__password,
    in_forename => :p102__forename,
    in_email    => case
                       when whatever then :p102__email
                       else somethingelse
                   end,
    in_tel      => :p102__tel,
    in_surname  => :p102__surname
);

Note that the labels on the left refer to the names of the arguments to which you are passing the values on the right, so it doesn't make sense to perform logic tests against in_email for example. Perhaps you wanted to do something depending on the value of :p102__email?

Answer 2

You need to use case instead of if

#OWNER#.REGISTER_CUSTOMER(
    IN_USERNAME => :P102__USERNAME,
    IN_PASSWORD => :P102__PASSWORD,
    IN_FORENAME => :P102__FORENAME,
    IN_EMAIL => CASE
    WHEN IN_EMAIL LIKE '@% study.beds.ac.uk'  THEN
       :P102__EMAIL
    end,
    IN_TEL => :P102__TEL,
    IN_SURNAME => :P102__SURNAME
);

The below sample will print bye. Since the first condition will fail.

begin
    dbms_output.put_line(a=> case
        when 1=2 then
           'hello'
        else 
           'bye'
        end);
end;

Answer 3

Well, since you tagged oracle-apex and you use page-items and such. Why are you skipping the use of a validation of the email and provide the user with some meaningful feedback? What's the other solution? Throw an ora-error? Blank it out? Create a validation on P102__EMAIL where you check the domain and if it doesn't match show a nice message such as 'The domain of your email is incorrect'. The user won't bee able to proceed otherwise, no matter how many times he'd try to submit.