usa-can zip&postal cose

From: Sebas Santos <sebas_at_shaw.wave.ca>
Date: Mon, 08 Feb 1999 06:44:11 GMT
Message-ID: <36BE8804.82EC7E98@shaw.wave.ca>

I created this two functions and the procedure. When I run I get the answers "function created" without errors, and procedure created without errors. However when I execute the procedure that calls the functions, I have a funny output error message. Can anyone help? Thanks in advance,
Sebastian

--FUNCTION TO CHECK FOR USA ZIP ONLY
--

CREATE OR REPLACE FUNCTION CHECK_ZIP (P_CODE IN VARCHAR2) RETURN NUMBER
IS
STR_IN VARCHAR2(7) := P_CODE;
BEGIN
   FOR I IN 1..5 LOOP

           IF SUBSTR(STR_IN, 1, I) NOT BETWEEN 0 AND 9
           THEN
                   RETURN 0;
                   EXIT;
           END IF;

   END LOOP;
   RETURN 1;
END;

/*******************************/

--FUNCTION TO CHECK FOR CANADIAN POSTAL CODE ONLY
--

 CREATE OR REPLACE FUNCTION CHECK_POSTAL (P_CODE IN VARCHAR2)  RETURN NUMBER
 IS

 STR_IN     VARCHAR2(7) := UPPER(P_CODE);
 STR_2      VARCHAR2(6);
 BEGIN
 IF         LENGTH(STR_IN) > 6 THEN

    STR_2 := SUBSTR(STR_IN, 1, 3)||SUBSTR(STR_IN, 4, 6);  END IF;
 IF SUBSTR(STR_2, 1, 1) BETWEEN 'A' AND 'Z' THEN     IF SUBSTR(STR_2, 1, 3) BETWEEN 'A' AND 'Z' THEN

       IF SUBSTR(STR_2, 1, 5) BETWEEN 'A' AND 'Z' THEN
     IF SUBSTR(STR_2, 1, 2) BETWEEN 0 AND 9 THEN
        IF SUBSTR(STR_2, 1, 4) BETWEEN 0 AND 9 THEN
           IF SUBSTR(STR_2, 1, 6) BETWEEN 0 AND 9 THEN
              RETURN 1;
              ELSE RETURN 0;
           END IF;
        END IF;
     END IF;
       END IF;

    END IF;
 END IF;
 END;

/********************************/

CREATE OR REPLACE PROCEDURE VAL_POSTAL (P_CODE IN VARCHAR2, P_STATUS OUT VARCHAR2)
IS
STR_IN VARCHAR2(10) := UPPER(P_CODE); CHECKER NUMBER;
BEGIN
IF LENGTH(STR_IN) = 5 THEN

           CHECKER := CHECK_ZIP(STR_IN);
                   IF CHECKER = 1 THEN
                           P_STATUS := 'THIS ZIP CODE IS VALID';
                   ELSE
                           P_STATUS := 'THIS ZIP CODE IS INVALID';
                   END IF;

--

ELSIF LENGTH(STR_IN) BETWEEN 6 AND 7 THEN --CHECK FOR CANADIAN POSTAL CODE
   IF INSTR(STR_IN, 'O') = 1 THEN
   P_STATUS := 'THIS IS NOT A VALID POSTAL CODE';    END IF;
   CHECKER := CHECK_POSTAL(STR_IN);

           IF CHECKER = 1 THEN
                   P_STATUS := 'THIS POSTAL CODE IS VALID';
                   ELSE
                   P_STATUS := 'THIS POSTAL CODE IS INVALID';
           END IF;

END IF;
END;
/*END OF THE VAL_POSTAL PROCEDURE Received on Mon Feb 08 1999 - 00:44:11 CST