Home » SQL & PL/SQL » SQL & PL/SQL » email validation (oracle 10g windowsXP)
email validation [message #309684] Fri, 28 March 2008 08:04 Go to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
In email column only accept email id format(balan@gmail.com) only.
if enter the other text error message will display like '
ENTER THE VALID EMAIL ID.send solution with code
Re: email validation [message #309688 is a reply to message #309684] Fri, 28 March 2008 08:17 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Does the email column have a CHECK constrain on it? Do you have any validation triggers defined?

Please provide more information and post the exact error message.
Re: email validation [message #309689 is a reply to message #309684] Fri, 28 March 2008 08:17 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Hahahaha

... oh, you weren't kidding?

[This was a reaction on the original post. How can you ask something like that from us? We don't even know if you want it in SQL, in PL/SQL, in Forms, in PHP, in Pro*C or in Visual Cobol.]

[Updated on: Fri, 28 March 2008 08:19]

Report message to a moderator

Re: email validation [message #309697 is a reply to message #309684] Fri, 28 March 2008 08:46 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
email id format(balan@gmail.com) only
What's invalid in "little.foot@mymail.com"? Why would you reject this format?

Perhaps you'd want to research use of regular expressions; these are rather powerful in such cases. Also, you have bad luck as I can't send solution with code.
Re: email validation [message #310010 is a reply to message #309697] Sun, 30 March 2008 23:33 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Validation Details:
In oracle forms I have created one text box, that text box name is called as E_MAIL.

In this textbox will accept only email format.. suppose we will give sweety@gmail.com.. that format of the email only accept the text box. suppose we will give simply sweet, that is not a format for e-mail... so simply the text box reject the illegal format. i need the code for accepting leagal e-mail format.. and displaying error message in illegal format.

send solution with code. have you understand my question?
Re: email validation [message #310012 is a reply to message #309689] Sun, 30 March 2008 23:36 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
oracle 10g forms...

i dont set check constraint.


Validation Details:
In oracle forms I have created one text box, that text box name is called as E_MAIL.

In this textbox will accept only email format.. suppose we will give sweety@gmail.com.. that format of the email only accept the text box. suppose we will give simply sweet, that is not a format for e-mail... so simply the text box reject the illegal format. i need the code for accepting leagal e-mail format.. and displaying error message in illegal format.

send solution with code. have you understand my question?
Re: email validation [message #310046 is a reply to message #310012] Mon, 31 March 2008 01:15 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Define what makes a string a valid email-address. No need to repeat your example; define a set of rules that you (and later your program) can follow to determine if a string is a valid email-address or not.
e.g.
- there needs to be an '@' in it
- there cannot be more than one '@' in it
- first position cannot be '@'

etc.

From there, you can translate that to whatever code you want
Re: email validation [message #310059 is a reply to message #310046] Mon, 31 March 2008 02:15 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There is a nice article on the trade-offs between more or less strict e-mail checking with regular expressions here :

http://www.regular-expressions.info/email.html

You could then use that regular expression to check the input.
Re: email validation [message #659064 is a reply to message #309684] Thu, 05 January 2017 07:43 Go to previous messageGo to next message
hasanjawaid
Messages: 2
Registered: January 2017
Junior Member
/*create below procedure in program unit and call where you want to validate your email id field*/
PROCEDURE check_email(val varchar2,FLD VARCHAR2,FLDLAB VARCHAR2) IS
alert number:=0;
ATHERAT NUMBER:=0;
DOTCOM NUMBER:=0;
BEGIN
	IF VAL is not null then
  if val not like '%@%' then
  	set_alert_property('ALT_MESSAGE',ALERT_MESSAGE_TEXT,'Invalid email mention in '||fldLAB);
  	ALERT:=SHOW_ALERT('ALT_MESSAGE');
  	GO_ITEM(FLD);
  	RAISE FORM_TRIGGER_FAILURE;
  END IF;
  ATHERAT := INSTR(val,'@');
  if UPPER(val) not like '%.COM%' then
  	set_alert_property('ALT_MESSAGE',ALERT_MESSAGE_TEXT,'Invalid email mention in '||fldLAB);
  	ALERT:=SHOW_ALERT('ALT_MESSAGE');
  	GO_ITEM(FLD);
  	RAISE FORM_TRIGGER_FAILURE;
  END IF;
  DOTCOM := INSTR(UPPER(val),'.COM');
  
  IF ATHERAT > DOTCOM THEN
  	set_alert_property('ALT_MESSAGE',ALERT_MESSAGE_TEXT,'Invalid email mention in '||fldLAB);
  	ALERT:=SHOW_ALERT('ALT_MESSAGE');
  	GO_ITEM(FLD);
  	RAISE FORM_TRIGGER_FAILURE;
  END IF;
  END IF;
END
*BlackSwan added {code} tags to improve readability. Please do so yourself in the future.

[Updated on: Thu, 05 January 2017 07:52] by Moderator

Report message to a moderator

Re: email validation [message #659066 is a reply to message #659064] Thu, 05 January 2017 07:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

You never get a second chance to make a First impression.
Do you think that OP has been waiting almost 9 years for this response?
Re: email validation [message #659067 is a reply to message #659064] Thu, 05 January 2017 08:13 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
hasanjawaid wrote on Thu, 05 January 2017 08:43
/*create below procedure in program unit and call where you want to validate your email id field*/
PROCEDURE check_email(val varchar2,FLD VARCHAR2,FLDLAB VARCHAR2) IS
alert number:=0;
ATHERAT NUMBER:=0;
DOTCOM NUMBER:=0;
BEGIN
	IF VAL is not null then
  if val not like '%@%' then
  	set_alert_property('ALT_MESSAGE',ALERT_MESSAGE_TEXT,'Invalid email mention in '||fldLAB);
  	ALERT:=SHOW_ALERT('ALT_MESSAGE');
  	GO_ITEM(FLD);
  	RAISE FORM_TRIGGER_FAILURE;
  END IF;
  ATHERAT := INSTR(val,'@');
  if UPPER(val) not like '%.COM%' then
  	set_alert_property('ALT_MESSAGE',ALERT_MESSAGE_TEXT,'Invalid email mention in '||fldLAB);
  	ALERT:=SHOW_ALERT('ALT_MESSAGE');
  	GO_ITEM(FLD);
  	RAISE FORM_TRIGGER_FAILURE;
  END IF;
  DOTCOM := INSTR(UPPER(val),'.COM');
  
  IF ATHERAT > DOTCOM THEN
  	set_alert_property('ALT_MESSAGE',ALERT_MESSAGE_TEXT,'Invalid email mention in '||fldLAB);
  	ALERT:=SHOW_ALERT('ALT_MESSAGE');
  	GO_ITEM(FLD);
  	RAISE FORM_TRIGGER_FAILURE;
  END IF;
  END IF;
END
*BlackSwan added {code} tags to improve readability. Please do so yourself in the future.
Unless this is example code, you can't restrict the domain to just .COM there are currently 886 legal domains (I have a daily job that goes to http://data.iana.org/TLD/tlds-alpha-by-domain.txt to maintain a list in an oracle table). Also if all you want to verify is the format then use regular expressions (see above link). They are much more reliable then your code example.

[Updated on: Thu, 05 January 2017 08:13]

Report message to a moderator

Re: email validation [message #659069 is a reply to message #659067] Thu, 05 January 2017 08:23 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
As it stands that code reduces to:
PROCEDURE check_email(val varchar2,FLD VARCHAR2,FLDLAB VARCHAR2) IS

BEGIN

  IF VAL is not null
  AND upper(val) not like '%@%.COM' then

    set_alert_property('ALT_MESSAGE',ALERT_MESSAGE_TEXT,'Invalid email mention in '||fldLAB);
    ALERT:=SHOW_ALERT('ALT_MESSAGE');
    GO_ITEM(FLD);
    RAISE FORM_TRIGGER_FAILURE;

  END IF;
  
END;

Also it's forms specific and this isn't the forms sub-forum.

Plus what Bill B said above.
Previous Topic: Collections - Nested Tables and VARRAY
Next Topic: Shrink space
Goto Forum:
  


Current Time: Thu Mar 28 16:49:53 CDT 2024