What am i doing wrong in this PLSQL function?

Question

I want to check if an email address given as an input is valid or not. I have created the following function. The function is created successfully but when I try running the function I am unable to get the desired output.

  CREATE OR REPLACE FUNCTION email_validate(p_email IN varchar2)
     return BOOLEAN
     is
     emailregexp constant varchar2(1000):= '^[a-z0-9._-]+@[a-z0-9.-]+\.[a-z]{1,10}$';

    BEGIN
     IF regexp_like(p_email,emailregexp ,'i') THEN
     RETURN true;
     ELSE
     RETURN false;
     END IF;
    END;

The way I am trying to check the output is:

DECLARE 

result BOOLEAN;

BEGIN 

result:= email_validate('abcd@gmail.com');
dbms_output.put_line(result);

END;

not sure about PLSQL regex, but you should probably escape the final `-` character inside the groups — devio, Nov 27 '16 at 15:01
The function is created successfully but I am not able to call the function like I want to run the function and see either True or False. — indexOutOfBounds, Nov 27 '16 at 15:06
"dbms_output.put_line is not overloaded to accept a boolean argument." http://stackoverflow.com/a/13560073/21336 — devio, Nov 27 '16 at 15:09
Not directly related to your problem, but your regex is going to reject valid email addresses. The most common ones it's going to reject are of the format `foo+bar@example.com`, but there are plenty of others. The best way to validate email addresses is really "don't even bother trying", and there aren't many compelling arguments against that. But meanwhile, go read http://stackoverflow.com/questions/201323/using-a-regular-expression-to-validate-an-email-address. — Jim Stewart, Nov 27 '16 at 15:42
You probably have enough info in the comments and answer to solve your problem already. As a "meta" observation, when you ask for help, saying something like "when I try running the function I am unable to get the desired result" is completely useless. Give an example: Here is an address I am trying to validate, it should be valid, but I get this result instead. Or, this address should be rejected, but the result I see is "valid". Or things along these lines. "My car is not working, what's wrong with it?" has a small chance at the garage. It has no chance in an online car help forum. — , Nov 27 '16 at 16:01

trincot · Accepted Answer · 2016-11-27T16:12:59.377

3

You cannot pass a boolean value as argument to dbms_output.put_line. When I run your code, I get this error:

ORA-06550: PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

The pragmatic solution is to let your function return a smallint (either 0 or 1), which will work just as well to perform your email address verification.

You can then test your code also more easily, like this:

select email_validate('abcd@gmail.com') from dual;

If you then want to display the result as either 'false' or 'true':

select case when email_validate('abcd@gmail.com') > 0 
            then 'true' 
            else 'false' 
       end
from dual;

If you stay within PL/SQL, you can do this with boolean type also (skip the > 0 then). Oracle SQL however cannot deal with it.

edited Nov 27 '16 at 16:12

answered Nov 27 '16 at 15:48

trincot

317,000
35
244
286

Yes I have been able to do it this way by outputting 1 or 0. The reason I asked this question was whether if we can do it by outputting either True or False somehow ? – indexOutOfBounds Nov 27 '16 at 16:01
@indexOutOfBounds If you plan to use always pl/sql "not in select statement" it will work. To test it with dbms_output, just do: DBMS_OUTPUT.PUT_LINE(CASE WHEN result THEN 'TRUE' ELSE 'FALSE' END). – Ftaveras Nov 27 '16 at 16:02
I just tried it and it works by this method. Thank you :) – indexOutOfBounds Nov 27 '16 at 16:56

What am i doing wrong in this PLSQL function?

1 Answers1