Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

Contact Us

Oracle Scripts
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 
Oracle delivers an API in EBS that can validate Email Address Format.
However it does not capture the invalidity if Email String were to be say xxx@host
You may use the below pl/sql function for Email Address Validation.


CREATE OR REPLACE FUNCTION xx_check_email(l_user_name IN VARCHAR2)
  RETURN VARCHAR2 IS
  l_dot_pos    NUMBER;
  l_at_pos     NUMBER;
  l_str_length NUMBER;
BEGIN
  l_dot_pos    := instr(l_user_name
                       ,'.');
  l_at_pos     := instr(l_user_name
                       ,'@');
  l_str_length := length(l_user_name);
  IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR
     (l_at_pos = 1) OR (l_at_pos = l_str_length) OR
     (l_dot_pos = l_str_length))
  THEN
    RETURN 'FAILURE';
  END IF;
  IF instr(substr(l_user_name
                 ,l_at_pos)
          ,'.') = 0
  THEN
    RETURN 'FAILURE';
  END IF;
  RETURN 'SUCCESS';
END xx_check_email;



Anil Passi

Comments   

0 #1 kalyan 2007-08-21 05:48
Its really helping.
Quote
0 #2 ramu 2008-02-01 13:16
what is the meaning of hwm(high watermask) in oracle?
Quote
0 #3 Sundar CPM 2008-06-25 15:21
Hi Anil,
Sorry for positing some irrelevant subject in this area. I am a novice in Oracle Apps. I have written the below query but the performance is not good. I am sure that I am missing some join conditions. Pls help me finding it out.
============== =============== =============== =======
SELECT GLB.set_of_book s_id, GLB.code_combin ation_id, gcc1.segment1,
ft1.description , gcc1.segment2, ft2.description , gcc1.segment3,
ft3.description , gcc1.segment4, ft4.description , gcc1.segment5,
ft5.description , gcc1.segment6, ft6.description , gcc1.segment7,
ft7.description , GLB.currency_co de, GLB.period_name , actual_flag,
budget_version_ id, translated_flag , revaluation_sta tus, period_type,
period_year, period_num, period_net_dr, period_net_cr,
period_to_date_ adb, quarter_to_date _dr, quarter_to_date _cr,
quarter_to_date _adb, year_to_date_ad b, project_to_date _dr,
project_to_date _cr, project_to_date _adb, begin_balance_d r,
begin_balance_c r, period_net_dr_b eq, period_net_cr_b eq,
begin_balance_d r_beq, begin_balance_c r_beq, GLB.template_id ,
quarter_to_date _dr_beq, quarter_to_date _cr_beq, project_to_date _dr_beq,
project_to_date _cr_beq, begin_balance_d r + period_net_dr,
begin_balance_c r + period_net_cr,
begin_balance_d r_beq + period_net_dr_b eq,
begin_balance_c r_beq + period_net_cr_b eq
FROM gl.gl_balances GLB,
apps.gl_code_co mbinations gcc1,
apps.fnd_flex_v alues fv1,
apps.fnd_flex_v alues_tl ft1,
apps.fnd_flex_v alues fv2,
apps.fnd_flex_v alues_tl ft2,
apps.fnd_flex_v alues fv3,
apps.fnd_flex_v alues_tl ft3,
apps.fnd_flex_v alues fv4,
apps.fnd_flex_v alues_tl ft4,
apps.fnd_flex_v alues fv5,
apps.fnd_flex_v alues_tl ft5,
apps.fnd_flex_v alues fv6,
apps.fnd_flex_v alues_tl ft6,
apps.fnd_flex_v alues fv7,
apps.fnd_flex_v alues_tl ft7
WHERE GLB.code_combin ation_id = gcc1.code_combi nation_id
AND GLB.actual_flag = 'A'
AND gcc1.chart_of_a ccounts_id = 101
AND gcc1.segment1 = fv1.flex_value
AND fv1.flex_value_ set_id = 1002722
AND fv1.flex_value_ id = ft1.flex_value_ id
AND ft1.LANGUAGE = 'US'
AND gcc1.segment2 = fv2.flex_value
AND fv2.flex_value_ set_id = 1002723
AND fv2.flex_value_ id = ft2.flex_value_ id
AND ft2.LANGUAGE = 'US'
AND gcc1.segment3 = fv3.flex_value
AND fv3.flex_value_ set_id = 1002724
AND fv3.flex_value_ id = ft3.flex_value_ id
AND ft3.LANGUAGE = 'US'
AND gcc1.segment4 = fv4.flex_value
AND fv4.flex_value_ set_id = 1002725
AND fv4.flex_value_ id = ft4.flex_value_ id
AND ft4.LANGUAGE = 'US'
AND gcc1.segment5 = fv5.flex_value
AND fv5.flex_value_ set_id = 1002726
AND fv5.flex_value_ id = ft5.flex_value_ id
AND ft5.LANGUAGE = 'US'

========= =============== =============== ============

T hanks,
Sundar
Quote
0 #4 Chaminda Kumara 2014-06-20 08:54
wk_validemail int(1);

begin
SELECT COUNT(*) into wk_validemail FROM dual WHERE REGEXP_LIKE (email, '^[A-Za-z0-9._% +-]+@[A-Za-z0-9 .-]+\.[A-Za-z]{ 2,4}$');
if wk_validemail = 0 then
return 'INVALID_EMAIL' ;
end if;

exception
when others then
return 'INVALID_EMAIL' ;
end;
Quote
0 #5 Priyanka 2015-01-08 10:30
Hi,
I want to validate the format of the email address entered in External iRecruitment page. I tried extending the controller of the page and in the PFR I wrote the code. But, it isn't working.
Please help.
Quote
0 #6 Priyanka 2015-01-08 10:57
I have written the following code in the controller--


package xxbr.oracle.apps.xxbr.PriIrc.server;

import oracle.apps.irc.candidateSelfService.webui.RegisterCO;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd .framework.OAAp plicationModule ;
import oracle.apps.fnd.framework.webui.beans.message.OAMessageTextIn putBean;
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.webui.beans.form.OASubmitButtonBean;

import java.sql.CallableStateme nt;
import java.sql.SQLException;
import java.sql.Types;

public class ExtendedRegiste rCO extends RegisterCO
{

public void processFormRequ est(OAPageConte xt oapagecontext, OAWebBean oawebbean)
{

String s = (String)oapagec ontext.getParam eter("Submit");

OAMessageTextIn putBean EmailCheck = (OAMessageTextIn putBean)oawebbean.findChildRecursive("Email");

if (s.equals("Subm it") && EmailCheck.getV alue(oapagecont ext) !=null)
{
String Email = EmailCheck.getV alue(oapagecont ext).toString();

String outParamValue;
OADBTransaction txn = oapagecontext.g etApplicationMo dule(oawebbean) .getOADBTransac tion();
CallableStateme nt cs = txn.createCallableStateme nt("begin :1 := XXBR_CHECK_EMAI L(:2); end;",1);

try
{
cs.registerOutP arameter(1, Types.VARCHAR);
cs.setString(2, Email);
cs.execute();
outParamValue = cs.getString(1) ;
cs.close();
}
catch (SQLException sqle)
{
throw OAException.wra pperException(s qle);
}

if (outParamValue. equals("FAILURE "))
{
throw new OAException(Ema il+" is in wrong format.It should be in the format - .Please correct it."+outParamVa lue, OAException.ERR OR);
}

}
super.processFo rmRequest(oapag econtext, oawebbean);
}

}

==================================================================================================================

pl/sql code is same as above---



CREATE OR REPLACE FUNCTION xxbr_check_emai l(l_user_name IN VARCHAR)
RETURN VARCHAR IS
l_dot_pos NUMBER;
l_at_pos NUMBER;
l_str_length NUMBER;
BEGIN
l_dot_pos := instr(l_user_na me
,'.');
l_at_pos := instr(l_user_na me
,'@');
l_str_length := length(l_user_n ame);
IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR
(l_at_pos = 1) OR (l_at_pos = l_str_length) OR
(l_dot_pos = l_str_length))
THEN
RETURN 'FAILURE';
END IF;
IF instr(substr(l_ user_name
,l_at_pos)
,'.') = 0
THEN
RETURN 'FAILURE';
END IF;
RETURN 'SUCCESS';
END xxbr_check_email;



=====================================================================================================================
Quote
0 #7 Muhammad Nadeem 2015-02-11 11:22
hi,
in case of space in email address, it will fail, the following is updated.

CREATE OR REPLACE FUNCTION xx_check_email( l_user_name IN VARCHAR2)
RETURN VARCHAR2 IS
l_dot_pos NUMBER;
l_at_pos NUMBER;
l_str_length NUMBER;
l_space NUMBER;
BEGIN
l_dot_pos := instr(l_user_na me
,'.');
l_space := instr(l_user_na me,' ');

l_at_pos := instr(l_user_na me
,'@');
l_str_length := length(l_user_n ame);
IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR
(l_at_pos = 1) OR (l_at_pos = l_str_length) OR
(l_dot_pos = l_str_length) OR l_space >0)
THEN
RETURN 'FAILURE';
END IF;
IF instr(substr(l_ user_name
,l_at_pos)
,'.') = 0
THEN
RETURN 'FAILURE';
END IF;
RETURN 'SUCCESS';
END xx_check_email;
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Aug 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
        1
  2  3  4  5  6  7  8
  9101112131415
16171819202122
23242526272829
3031     

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner