Home » SQL & PL/SQL » SQL & PL/SQL » Validate a Field
Validate a Field [message #200826] Wed, 01 November 2006 08:38 Go to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
I am validating data for a table.

I have a column I use to mark that a row should not be used.

One of the fields I need to validate is zipcode.

I know probably need to build a procedure but am not sure how to go about it.

I need to build a function that checks to see whether the zip is completely numeric for USA residents, or a combination of alpha numeric for Canadian, we have a column to distinguish country. If either of these are false or the field contains symbols etc then it needs to populate the elimination flag.

Someone have some pointers or an example I can modify...
Re: Validate a Field [message #200987 is a reply to message #200826] Thu, 02 November 2006 02:36 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you'd like to distinguish valid from invalid entries during insert (or update), a database trigger on a table might do what you need. Here's a quick example:
CREATE TABLE TEST (COUNTRY VARCHAR2(3), zip_code VARCHAR2(10), OK VARCHAR2(1));

CREATE OR REPLACE TRIGGER trg_zip
  BEFORE INSERT OR UPDATE ON TEST
  FOR EACH ROW
DECLARE
  l_zip TEST.zip_code%TYPE;
BEGIN
  IF :NEW.COUNTRY = 'USA' THEN
     BEGIN
       SELECT TO_NUMBER(:NEW.zip_code)
	   INTO l_zip
	   FROM dual;
	 :NEW.ok := 1;
     EXCEPTION
	 WHEN OTHERS THEN :NEW.ok := 0;
     END;
  END IF;
END;
/		

INSERT INTO TEST (COUNTRY, zip_code) VALUES ('USA', '12345');

INSERT INTO TEST (COUNTRY, zip_code) VALUES ('USA', 'a12345');

I'll leave Canadian part to you Smile
Previous Topic: Ordering question
Next Topic: How to update a table with subquery returning more than one row
Goto Forum:
  


Current Time: Fri Dec 09 03:42:48 CST 2016

Total time taken to generate the page: 0.24342 seconds