Date field checking with plsql

with t as (select '01/02/2010' as dt from dual union all
select '04/03/2010' from dual union all
select '05/30/2010' from dual union all
select '30/01/2010' from dual)-- end of test data
select dt
,case when regexp_like(dt,'^[0-3][0-9]\/[0-1][0-9]/(19|20)[0-9]{2}$')
then to_date(dt, 'DD/MM/YYYY')
else null
end as dt_date
,case when not regexp_like(dt,'^[0-3][0-9]\/[0-1][0-9]/(19|20)[0-9]{2}$')
then 'Invalid Format'
else 'Ok'
end as dt_status
from t


2)


create or replace function test_date(dt IN VARCHAR2) return varchar2 is
v_dt DATE;
begin
v_dt := to_date(dt,'DD/MM/YYYY');
return 'Ok';
exception
when others then
return 'Error';
end;

/

Function created.


with t as (select '01/02/2010' as dt from dual union all
select '04/03/2010' from dual union all
select '05/30/2010' from dual union all
select '30/01/2010' from dual) -- end of test data
select dt, test_date(dt) as status
from t
/


3)


CREATE OR REPLACE FUNCTION IS_DATE(F_DATE VARCHAR2, F_FORMAT VARCHAR2 DEFAULT 'DD-MON-YYYY')
RETURN VARCHAR2
IS
V_DATE DATE;
BEGIN
V_DATE := TO_DATE(F_DATE, F_FORMAT);
RETURN V_DATE;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RETURN NULL;
END;

SELECT ('01-JAN-2010','DD-MON-YYYY') DATE_CHECK FROM DUAL;

How to check valid email address

checking valid email address
=======================================

CREATE OR REPLACE PROCEDURE EMAIL_CHECK(EMAIL_ID VARCHAR2)
AS
VALID NUMBER;
BEGIN
SELECT 1
INTO VALID
FROM
(SELECT EMAIL_ID FROM DUAL)
WHERE REGEXP_LIKE(EMAIL_ID,'.*\@.*\..*');
IF VALID = 1 THEN
DBMS_OUTPUT.PUT_LINE('EMAIL ID ' || EMAIL_ID || ' IS NOT VALID');
ELSE
DBMS_OUTPUT.PUT_LINE('EMAIL ID ' || EMAIL_ID || ' IS VALID');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EMAIL ID ' || EMAIL_ID || ' IS NOT VALID');
END;



CREATE OR REPLACE PROCEDURE EMAIL_CHECK(EMAIL_ID VARCHAR2)
AS
BEGIN
IF OWA_PATTERN.MATCH(EMAIL_ID, '.*\@.*\..*')
THEN
DBMS_OUTPUT.PUT_LINE('Valid Email');
ELSE
DBMS_OUTPUT.PUT_LINE('InValid Email');
END IF;
END;

Data Encryption - DBMS_OBFUSCATION_TOOLKIT

ORACLE-BASE - Data Encryption - DBMS_OBFUSCATION_TOOLKIT

Secure Application Roles

Secure Application Roles

Fine Grained Auditing

Fine Grained Auditing

Transparent Data Encryption

Transparent Data Encryption

Menu list of query of my software (tree node)

select count(progtype)
from (

select de,progname,progtype from (
select a.*,b.PROGTYPE from (
select PARENTNM, levlcode, decode(levlcode,1,progdesc,2,' '||progdesc,3,' '||progdesc,4,' '||progdesc,' '||progdesc) de,
decode(levlcode, 1,'nxtblk',2,'nxtblk',3,'nxtblk',4,'paste','paste') leave,progname ,MENUSLNO
from symentre
where levlcode != 0
start with PARENTNM='ROOTP'
connect by prior progname=parentnm
) a, symenmas b
where a.progname =b.progname(+)
)
start with PARENTNM='ROOT'
connect by prior progname=parentnm


)
where nvl(progtype,'X') ='R'