PL/SQL input validation technique / utils

Question

Could you please suggest me common PL/SQL input validation technique / utility: digits only, string size restriction, email address, SSN, etc. I've made some google search, but didn't find any library package or source code. I don't want to re-invent the wheel :)

Thank you for your help.

What version of Oracle? What version of Forms? Are you trying to validate the data in Forms? Or in stored procedures in the database? — Justin Cave
– Justin Cave, Commented Jul 26, 2011 at 18:29
Oracle-11, need to validate input "foo & bar" like: "mysite.com?foo=123&bar=xyz" in stored procedures. Thanks. — vadimk
– vadimk, Commented Jul 26, 2011 at 18:35
to Codo: thank you for suggestion Codo. If I don't find any utility I'll use this technique. — vadimk
– vadimk, Commented Jul 26, 2011 at 18:48

janbom · Accepted Answer · 2011-08-02 06:39:33Z

3

depending on what version of oracle and/or forms i would defenetly use regexp_like if i'd need to validate a string like "mysite.com?foo=123&bar=xyz"

answered Aug 2, 2011 at 6:39

janbom

1191 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aitor · Accepted Answer · 2011-08-02 06:58:02Z

0

There's a script named dbsmany.sql that perhaps you can find useful in {ORACLE_HOME}/rdbms/admin/dbmsany.sql

Is not exactly what you're looking for, but has an interesting approach, the anydata type.

CREATE OR REPLACE FUNCTION getData(p_x IN sys.anyData)
RETURN VARCHAR2 IS 
 l_num      NUMBER;
 l_date     DATE;
 l_varchar2 VARCHAR2(4000); 
BEGIN
  CASE p_x.gettypeName
  WHEN 'SYS.NUMBER' THEN
    IF (p_x.getNumber(l_num) = dbms_types.success) THEN
      l_varchar2 := l_num;
    END IF;
  WHEN 'SYS.DATE' THEN
    IF (p_x.getDate(l_date) = dbms_types.success) THEN
      l_varchar2 := l_date;
    END IF;
  WHEN 'SYS.VARCHAR2' THEN
    IF (p_x.getVarchar2(l_varchar2) = dbms_types.success) THEN
      NULL;
    END IF;
  ELSE
    l_varchar2 := '** unknown **';
  END CASE;

  RETURN l_varchar2;
END getData;
/

For more public and useful PL/SQL scripts: http://psoug.org/browse.htm

answered Aug 2, 2011 at 6:58

Aitor

3,4692 gold badges31 silver badges34 bronze badges

1 Comment

vadimk Over a year ago

well... it's a bit different from what I was looking for. But thanks anyway.

Collectives™ on Stack Overflow

PL/SQL input validation technique / utils

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related