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;
Apps2Fusion Articles 
Comments
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
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;
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.
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 - abc
}
}
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;
=====================================================================================================================
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;
RSS feed for comments to this post