[oraculous]

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!!!

[andrewst]

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!

[oraculous]

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!!

[andrewst]

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;

[oraculous]

thank you so much!! i didnt even think about translate...you are a great help...it is appreciated!!

[oraculous]

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;
/

[andrewst]

You can use a FOR loop:

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.

[oraculous]

oooOOHH thanks man, you are a great help!!