SQL & PL/SQL :: REGEXP_LIKE Function For Valid Email Address Validation

Jun 2, 2011

I am writing a REGEXP_LIKE function to validate the email address.. below function works for most of the valid email address, but not working for below condition... not sure how it can be tweaked to work for below condition as well...

Character . (dot, period, full stop) provided that it is not the first or last character, and provided also that it does not appear two or more times consecutively (e.g. John..Doe@example.com).

Select 'X' from dual
WHERE NOT REGEXP_LIKE('John.Doe@example.com','^(([a-zA-Z0-9_-])([a-zA-Z0-9_.''-]*)@([((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9]).){3}|((([a-zA-Z0-9-]+).)+))([a-zA-Z]{2,}|(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])]))$')

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: String Function - Extract Domain From Email Address

Oct 12, 2011

I'm trying to extract the domain from email addresses like from clay500@msn.com I want to pull msn

I figured I would use the POSITION function, but it fails, even when I cut and pasted the example from the syntax guide

SELECT POSITION ('CAT' IN 'CATCH') FROM DUAL;

the guide says you get the answer 1

but I get ORA-00907: missing right parenthesis

What gives?

View 8 Replies View Related

SQL & PL/SQL :: Insert Data Into Valid Table When Validation Fulfilled?

Feb 19, 2013

I have a requirement like to validate the data in PL/SQL script dynamically.

I have 4 tables

TEST_TBL: Data available in this table
TEST_VALID_TBL: Contains field names of TEST_TBL and condition
VALID: Need to insert valid records
INVALID: Need to insert invalid records

I have to insert data into valid table when validation are full filled otherwise it should be insert invalid table .

Validation are based on TEST_VALID_TBL

While checking the data validations FIELD_NAME should be passed dynamically,because i have four columns in TEST_TBL but at present I am validating only 3 columns in feature it may be add more columns to validate.

View 4 Replies View Related

SQL & PL/SQL :: Does Oracle 10g Supports Regexp_Like Function

Jan 6, 2009

I am using REGEXP_LIKE function, but it is giving me error like this.

SQL>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.

SQL>select regexp_like(testcol,'^ab[cd]ef$') from test;
select regexp_like(testcol,'^ab[cd]ef$') from test
*
ERROR at line 1:
ORA-00904: "REGEXP_LIKE": invalid identifier

But in Oracle documentation it is given under heading of 10g. Does Oracle 10g supports this function?

View 11 Replies View Related

Forms :: IP Address Validation

Feb 18, 2007

Has any one written the validation code for

IP Address...

i.e it should be any thing from 0.0.0.0 to 255.255.255.255

View 6 Replies View Related

Application Express :: Validation On Text Item / User Enter Valid Date Format

Oct 13, 2012

i have text item :P1_ADMISSION_DATE .i want to validate that item if user does not enter valid date forrmat then display me error.

Date Format is 'DD-MON-YYYY'

i have try with PL/SQL Expression in Validation

My validation code

:P1_ADMISSION_DATE=TO_CHAR(SYSDATE,'DD-MON-YYYY');How can i validate :P1_ADMISSION_DATE to enter valid date format by user.

View 2 Replies View Related

Forms :: Validate Field To Be Valid Email ID?

Feb 16, 2011

want to validate a field to be a valid email id.

View 1 Replies View Related

Implement RFC822 / RFC1035 For Email Validation In PL/SQL?

Aug 29, 2003

i know that pl/sql's strong point is not regular expressions.given my requirements per employer, email validation will need to be done in the pl/sql layer. i'd like to implement RFC822/RFC1035 for email validation

View 9 Replies View Related

SQL & PL/SQL :: How To Get Email Address From Xml

Dec 27, 2011

I want to know how can I find/take from In parameter who is xmltype an email address ?

My function: (clob is cast to xmltype)

function submit(session_id in number, claim in clob) return xmltype

For example - I have more e-mail addresses in this:

<emails>
wtfn00b1@yahoo.com
wtfn00b2@inbox.com
wtfn00b3@mail.com
...
</emails>

I know that there is `extract` function for xml. How can I know if there is break or space between the emails ?

View 9 Replies View Related

Getting Name In Email Address (Oracle SQL Plus)

Nov 6, 2007

How do I select the name in an email address. I would like to be able to order a list of email addresses by the name of the person ie. what comes before the @ sign.

So is there a query I could use to get everything before the at line . So in pseudocode I would need to be able to do something like this.

select substr(email, indexOf(@)+1,email.length) from table order by email

So I how could I go about doing that?

View 2 Replies View Related

SQL & PL/SQL :: Allowing Only Email Address In The Field

Jan 23, 2011

I want only one email address should be entered in the Alphanumeric field and not any words or phrases or spaces or any other email address.

How to give restriction for this.

View 39 Replies View Related

SQL & PL/SQL :: Extract Domain Name From Email Address Field?

Nov 29, 2011

I have a field for email address. I would like to create a field to capture just the domain (after the @ and before the .com) so I can process a count of records by domain.

View 17 Replies View Related

Forms :: Declare Message For @ Is Not Type In Email Address

Oct 24, 2011

i want if user is not type the @ in email text box than from show the message "check your email address" but i cant do that

declare
ok_flag number :=0;
begin
ok_flag :=instr('emailaddress','@');
if
ok_flag < 1 then
message('invalid email');
else
message('thanks');
end if;
end;

View 8 Replies View Related

Created Registration Form With Input Field Of Email Address

May 30, 2011

i have created a registration form with an input field of email address. now i want if a new user register, it should automatically receive a confirmation mail.

i have done some coding but i am unable to define what to write on place of highlighted text.

public void sendConfEmail(){
try{
Properties props = null;
if (props == null) {
props = System.getProperties();
}
props.put("mail.smtp.host", "<server host name>");
Session session = Session.getInstance(props, null);
[code]..

View 4 Replies View Related

SQL & PL/SQL :: Function That Accepts 2 Dates - Not A Valid Month Error

Apr 15, 2011

I have function that accepts 2 dates namely sDate and eDate i.e start and end dates. See the declaration part of the function:

startDate eab.eod_date%type;
endDate eab.eod_date%type;

begin
startDate := to_date(sDate,'DD-MON-YY');
endDate := to_date(eDate,'DD-MON-YY');
end

When I called the function it gives me the following error: ORA-01843: not a valid month..And I tried to simulate the process, the below code also show the same error:

select TO_DATE(to_date('13-DEC-09', 'dd-MON-yy', 'nls_date_language=english'),'DD-moN-YY') from dual;

how I can check that error. I need to pass dates to my function.

View 8 Replies View Related

Application Express :: Validation - PLSQL Function Returning Boolean Bug?

Mar 12, 2013

Application Express 4.1.1.00.23 ( plus all earlier versions that I've ever used)

When using the wizard to create a Validation of type "PLSQL Function returning Boolean", why is it mandatory to enter a value in the text field "Error Message" on the screen that follows? This message is never used as the message actually displayed comes from a PLSQL return statement.

View 5 Replies View Related

SQL & PL/SQL :: Display Office Address / House Address And Emp ID In Single Query?

Feb 23, 2013

I have two tables emp , emp address as below

emp table :
emp_id emp_name,
1 rajesh
2 suresh

emp address table :

emp id emp_addresstype emp address
1 O kukatpally
1 H Hitech
2 O Kolkata
2 H hydar nagar

I need the query to display the Office address & House address & emp id in single query

ex: 1,Kukatally,hitech
2,kolkata,hydarnagr

View 3 Replies View Related

PL/SQL :: REGEXP_LIKE ORA-00920 Invalid Relational Operator

Apr 18, 2013

I am trying to use the REGEXP_LIKE and I am getting ORA-00920: invalid relational operator.

I have run very simplified code to try to sort it out but no success:

SELECT id, address
FROM test_lob_tab
WHERE REGEXP_LIKE(address,'^A');

select 1 from dual where regexp_like('123', '^[0-9]$');

same result from both.

I am using Oracle 11 and freetoad 11.

View 5 Replies View Related

SQL & PL/SQL :: Connect Tables Using Valid From / To

Dec 13, 2011

I would like to join 2 tables which are both using valid_from valid_to.

CREATE TABLE TEST_HIST1
(
ID INTEGER,
DESC VARCHAR2(7 BYTE),
VALID_FROM DATE,
VALID_TO DATE

[code]...

The end result I would like to see is:

ID | Name | Descr | Valid_from | Valid_to
1 | Name1 | Descr1 | 2001/01/01 | 2001/12/31
1 | Name2 | Descr2 | 2002/01/01 | 2002/12/31
1 | Name2 | Descr3 | 2002/06/01 | 2002/12/31
1 | Name3 | Descr3 | 2003/01/01 | 2003/12/31
1 | Name3 | Descr4 | 2004/01/01 | 3000/12/31

View 10 Replies View Related

SQL & PL/SQL :: ORA-01843 / Not A Valid Month

Jul 25, 2011

I have a date field with varchar datatype. My data look like :

ID Amend_DATE
1 4/24/1974
1 4/18/2001
1 8/2/1971
2 8/4/1965
3 11/9/2001
4 8/15/2001

I need to find the minimum amend_date for each ID.When i calculate min(amend_date), it gives me error:

ORA-01843 : not a valid month

Query i am using is :

select min(to_date(amend_date ,'mm/dd/yyyy') ) from t group by id;

My data on application appears as 24 April 1974.

View 2 Replies View Related

ORA-01843 / Not A Valid Month

Sep 14, 2012

I have 2 cursors. Cursor 1 selects a number and 2 dates (values in the Table are in the format mm/dd/yyyy for both the columns).

I pass these values to Cursor 2.

v_promotion_id promotion.promotion_id%TYPE;
v_promotion_start_date promotion.start_date%TYPE;
v_promotion_end_date promotion.end_date%TYPE;
CURSOR c_promotion_list IS
(SELECT DISTINCT p.promotion_id,
p.start_date - 5,
p.end_date + 5
FROM promotion p,

[code]....

I am getting this error: ORA-01843: not a valid month

Initially I was passing just the variables without To_date. But I got the same error.

View 2 Replies View Related

SQL & PL/SQL :: How To Get Valid Data After Skip Bad Block

Mar 19, 2011

I have a table and no related backup available.If some block is broken, then , how can I get the data with the blocks that are not broken?

Test environment:

SQL> create tablespace test datafile '/u01/app/oracle/oradata/test/test.dbf' size 1m;

Tablespace created.

SQL> create table scott.test_bad_block (id int, name varchar2(20)) tablespace test;

Table created.

SQL> r
1 begin
2 for i in 1..1000000 loop
3 insert into scott.test_bad_block values (i, 'test' || i);
4 commit;
5 end loop;
6* end;
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.TEST_BAD_BLOCK by 8 in tablespace TEST
ORA-06512: at line 3
SQL> select count(*) from scott.test_bad_block;

COUNT(*)
----------
40984

Now I know, I have 40984 rows in the table.In order to simulating the block corrupt, I shutdown the database and I used the bvi (a binary file editor to modify a line of record) , then start the database, when I am issuing the same statement, the problem shows:

SQL> select count(*) from scott.test_bad_block;
select count(*) from scott.test_bad_block *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted[b] (file # 7, block # 72)[/b]
ORA-01110: data file 7: '/u01/app/oracle/oradata/test/test.dbf'

It says that the block is corrupted.Now, here is what I can do to get the data right now:

#get the object id
SQL> r
1* select OBJECT_NAME, DATA_OBJECT_ID from dba_objects where owner='SCOTT' and object_name='TEST_BAD_BLOCK'

OBJECT_NAME DATA_OBJECT_ID
-------------------- --------------
TEST_BAD_BLOCK 10327
[code]....

Here are my questions:

1. How can I know, that if there is valid data(the block is not corrupted) after this bad block?
2. How can I get the data after this bad block?

View 21 Replies View Related

SQL & PL/SQL :: ORA-01839 - Date Not Valid For Month Specified?

Nov 1, 2013

our web application has a field DATETO which has no validation (end date). So user can put garbage there. In this field should be SYSDATE + max. 2 years. But also SYSDATE + 1 month OK.

I need to make a script that sets for example "31.12.9999" to null. Because so long end date is non sense.

SQL> SELECT EXTRACT (YEAR FROM (TO_DATE (DATETO, 'DD.MM.YYYY')))
FROM XXX_USER_JC_ORDERID
WHERE DATETO IS NOT NULL;
ERROR:
ORA-01839: date not valid for month specified

no rows selected

Littlefoot writes about ORA-01839 "It usually happens when invalid dates are used, such as 30th of February"[URL]..

I don't see invalid date???

SQL> SELECT DATETO
FROM XXX_USER_JC_ORDERID
WHERE DATETO IS NOT NULL;
DATETO
--------------------------------------------------------------------------------
23.09.2013

[code]...

DATETO
--------------------------------------------------------------------------------
31.12.2014
31.12.2016
31.12.2013
31.08.2014

[code]...

View 7 Replies View Related

SQL & PL/SQL :: ORA-24247 With Valid ACL When Sending Smtp Mail?

Aug 1, 2012

I have users with the same privileges to the same ACL and some can send emails and some others cannot !

In principle I created the ACL for USER1:

BEGIN
dbms_network_acl_admin.create_acl
(acl=> 'mails_senden.xml',
description=> 'Mails senden ueber INTRANET Mail-Server ...',
principal=> 'USER1',
is_grant=> true,
privilege=> 'connect');
COMMIT;

[code]....

Then I added USER2:

BEGIN
dbms_network_acl_admin.add_privilege
(acl=> 'mails_senden.xml',
principal=> 'USER2',
is_grant=> true,
privilege=> 'connect');

[code]....

USER1 sent a mail via UTL_SMTP successful whereas USER2 got the errors:

DECLARE
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267

[code]....

There is still the following fact:

USER1 has the role DBA, USER2 got the EXECUTE privilege to packages UTL_TCP, UTL_SMTP (GRANT from SYS).

IF the role DBA was granted to USER2 too then he couldt send emails just as well as USER1.

View 7 Replies View Related

SQL & PL/SQL :: Time Conversion - Not A Valid Month Error

Aug 16, 2010

why this query is not working

SQL> select xid,start_time from v$transaction;

XID START_TIME
---------------- --------------------
070006009A010000 08/16/10 13:24:43

SQL> select xid,start_time from v$transaction where start_time>to_timestamp('08/16/2010 12:15:00','mm/dd/yyyy hh24:mi:ss');
select xid,start_time from v$transaction where start_time>to_timestamp('08/16/2010 12:15:00','mm/dd/yyyy hh24:mi:ss')
*
ERROR at line 1: ORA-01843: not a valid month

View 17 Replies View Related

SQL & PL/SQL :: Duplicated Entries In A Valid Unique Index?

May 26, 2010

I'm having problem with my database, which contains more than 1 rows with a same value on a field that has uniqueness contraint.

Here is the log from sqlplus. When I select on RI field, it shows 2 rows. But when I select on SCNUM field, it shows only 1 row. This SCNUM has an unique index on it.

And it is still in VALID state

SQL> set autotrace on
SQL> select ri, scnum from scratch1_p where ri in (536964983, 536955574);
select ri from scratch1_p where scnum='444393975';
RI SCNUM
---------- ----------
536955574 444393975

[code].....

View 14 Replies View Related

Forms :: Verification That User Selected Valid Date

Mar 19, 2013

I have 3 types list items:

1) :1 Day -> 31
2) Month: 1 -> 12
3) Year :1990 -> 2013

I want to check that the user has selected a valid date .. if not an error message will be displayed, for example: 31-02-2013: will be rejected

View 9 Replies View Related

Server Utilities :: IMP-00010 Not A Valid Export File

Oct 7, 2011

IMP-00010 not a valid export file, header failed verification.I want to ask that can I take Import of .dmp file from 64-bit to 32-bit machine

View 2 Replies View Related

Forms :: Frm-15500 Valid And Unique Object Name Must Be Entered

Feb 28, 2012

I'm trying to add a column in an existing form. I added a text item and defined it as a database item. When I tried to rename the item the message (frm-15500 valid and unique object name must be entered)appears, and therefore not able to exit the property Platte.

Is there a way I can exit the Platte and should I've first add the column to the data block and then rename in my form ?

View 1 Replies View Related

Server Utilities :: Error Not Valid Month In Sql Loader?

May 17, 2012

I am trying to load below data using sql loader.

05/17/12,07:45:39,resn-j35-ctc113,"USR:ESM.CQueryTypeDlg::RunQuery().wft",0.125,P,schapptbook,22712,25704,705355992045,RBA4010200,10.146.218.154,0, "",0,"","",""

table structure
CREATE TABLE TEMP_CERNER_RESP_TIME_LND
(
INSTALLATION_ID VARCHAR2(50 BYTE) NULL,
TRANSACTION_ID VARCHAR2(50 BYTE) NULL,
SERVER_ID VARCHAR2(50 BYTE) NULL,

[code]...

Below function has been used to transfor data and callled in sql loader control file
CREATE OR REPLACE function return_domain( domain_name varchar2)
return varchar2
as
v_dmn varchar2(100)

[code]...

sql loader control file is as below:

load data
BADFILE '/backup/temp/rajesh/CERNER/BadFiles/FILENAME'
append into table TEMP_CERNER_RESP_TIME_LND
WHEN CLINICAL_TRANSACTION_ID = 'USR:ERM PMSEARCH ENCOUNTER RESULTS DISPLAY'
TRAILING NULLCOLS

[code]...

function takes the parameter as 'DOMAIN50_LPAR5002_slainterval051712_rj35cmi102_08_45_00.csv '

FILENAME in control file will be replace by DOMAIN50_LPAR5002_slainterval051712_rj35cmi102_08_45_00.csv when i run the the the loader i get the below error.

Record 1: Rejected - Error on table TEMP_CERNER_RESP_TIME_LND.
ORA-00604: error occurred at recursive SQL level 1
ORA-01843: not a valid month

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved