Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Posts
    7

    Unanswered: Help! this is so basic for some of you!!!

    HI! i am a college student and i need help with my PL/SQL project. we have to create a package with subprograms (1 private and 1 public).

    the public function will accept a SSN as a parameter and return a formatted SSN (###-##-####) or an error message that identifies what test failed and why the SSN was not good, no testing will occur in the public function.

    the public function will call a private function/procedure in the same package. the private program unit will test a parameter that is passed in by the public function:
    --the parameter passed in may not contain any alphas
    --may not contain all the same numbers (111-11-1111)
    --will accept only the following values as good:
    123456789
    123-45-6789
    123-456789
    12345-6789

    create a table called Test_SSN with one column (varchar2(11)). create a database trigger that calls your packaged function to test the SSN before inserting it in the table.


    If you have ANY suggestions, PLEASE RESPOND!!! My teacher gave me a hint to use counter:=counter + 1. HELP HELP HELP!!!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Help! this is so basic for some of you!!!

    Post what you have managed to do yourself, and people can offer suggestions and corrections. There is no educational value in subcontracting your homework to others!

  3. #3
    Join Date
    Apr 2003
    Posts
    7
    Sorry, i wasnt trying to get someone to do it for me, ok, i have made a couple of tests, they are anonymous blocks right now, but they will be part of my functions in my package..

    DECLARE
    SSN VARCHAR(100) := '123-45-6789';
    BEGIN
    IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN
    RAISE_APPLICATION_ERROR(-20001,'SSN MUST BE BETWEEN 9 AND 11 CHARACTERS');
    ELSE
    IF LENGTH(SSN) = 11 AND
    SUBSTR(SSN, '-') = 4 AND
    SUBSTR(SSN, '-',2) =7 THEN
    DSMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
    ELSE
    IF LENGTH (SSN) = 10 AND
    SUBSTR(SSN, '-') = 4 OR
    SUBSTR(SSN, '-') = 6 THEN
    DBMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
    ELSE
    IF LENGTH = 9 AND
    SSN NOT LIKE '%-%' THEN
    DBMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
    ELSE RAISE_APPLICATION_ERROR(-20001,'HYPHEN ENTERED
    INCORRECTLY');
    END IF;
    END IF;
    END IF;
    END IF;
    END;
    /


    To test to make sure the SSN does not contain any alphas, must i repeatedly continue on like this : SSN NOT LIKE '%a%' AND SSN NOT LIKE '%b%' ....... and so on? Any help will be Extremely Appreciated!!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by oraculous
    To test to make sure the SSN does not contain any alphas, must i repeatedly continue on like this : SSN NOT LIKE '%a%' AND SSN NOT LIKE '%b%' ....... and so on? Any help will be Extremely Appreciated!!
    The TRANSLATE function will be helpful here:

    TRANSLATE( ssn, 'x0123456789-', 'x' )

    This will translate all digits and '-' to NULL, leaving behind any other (invalid) characters. So if the result is NOT NULL that means there were some invalid characters in the string.

    The 'x' is just there as a dummy, to prevent the 3rd argument being '', which doesn't work. It gets translated to 'x', so doesn't affect the result.

    You could test and display the invalid chars like this:

    v_invalid VARCHAR2(11);
    ...
    v_invalid := TRANSLATE( ssn, 'x0123456789-', 'x' );
    if v_invalid is not null then
    raise_application_error(-20001,'Invalid chars in SSN: '||v_invalid);
    end if;

  5. #5
    Join Date
    Apr 2003
    Posts
    7
    thank you so much!! i didnt even think about translate...you are a great help...it is appreciated!!

  6. #6
    Join Date
    Apr 2003
    Posts
    7
    Does anyone know how i can use a loop if i use something like TEMP_SSN VARCHAR2 := SUBSTR(SSN,1,3)||SUBSTR(SSN,5,2)||SUBSTR(SSN,8,4) and then use a counter:=counter + 1...??????


    does anyone know how i could use this or if it would work here instead of using all those [SUBSTR(SSN,1) <> '-' AND]......?????


    CREATE PROCEDURE SSN_PROC (SSN VARCHAR2)
    -- SSN VARCHAR(100) := '123-45-6789';
    SSN_INVALID VARCHAR2 (11):=TRANSLATE(SSN,'x0123456789-','x');
    TEMP_SSN VARCHAR2;
    BEGIN
    IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN
    RAISE_APPLICATION_ERROR(-20001,'SSN Must Be Between 9 And 11 Characters');
    ELSE
    IF LENGTH(SSN) = 11 AND
    INSTR(SSN, '-') = 4 AND
    INSTR(SSN, '-',1,2) =7 AND
    SUBSTR(SSN,1) <> '-' AND
    SUBSTR(SSN,2) <> '-' AND
    SUBSTR(SSN,3) <> '-' AND
    SUBSTR(SSN,5) <> '-' AND
    SUBSTR(SSN,6) <> '-' AND
    SUBSTR(SSN,8) <> '-' AND
    SUBSTR(SSN,9) <> '-' AND
    SUBSTR(SSN,10) <> '-' AND
    SUBSTR(SSN,11) <> '-' THEN
    DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
    ELSE
    IF LENGTH (SSN) = 10 AND
    INSTR(SSN, '-') = 4 OR
    INSTR(SSN, '-') = 6 THEN
    DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
    ELSE
    IF LENGTH = 9 AND
    SSN NOT LIKE '%-%' THEN
    DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
    ELSE RAISE_APPLICATION_ERROR(-20001,'Hyphen Entered Incorrectly');
    IF SSN_INVALID IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20001,'Invalid Characters In SSN:'||SSN_INVALID);
    END IF;
    END IF;
    END IF;
    END IF;
    END IF;
    END;
    /

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can use a FOR loop:

    Code:
    FOR i IN 1..11 LOOP
      IF i NOT IN (4,7) AND SUBSTR(SSN,i,1) = '-' THEN
          RAISE_APPLICATION_ERROR(-20001,'Hyphen Entered Incorrectly');
      END IF;
    END LOOP;
    Note the 3rd parameter to SUBSTR, i.e. the length of the substring required. You had SUBSTR(SSN,1) which is the substring from 1 to end of SSN, i.e. is equal to SSN.

  8. #8
    Join Date
    Apr 2003
    Posts
    7

    Smile

    oooOOHH thanks man, you are a great help!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •