[oraculous]

I wrote a package that checks SSN's that can only be entered in 4 ways. I completed everything I just can't get the SSN to appear when a user enters in a SSN with length 10 or 9.

How do i get the formatted SSN to dbms output after i initialize it to the formatted SSN????

All I have to format is this--> SSN:= SUBSTR(SSN,1,6)||'-'||SUBSTR(SSN,7,4); But I need the formatted SSN to appear if after a user enters it.

I have set serveroutput on, and it works if length is 11!!!!

CREATE OR REPLACE PACKAGE BODY SSN_PACKAGE --package body
IS
PROCEDURE SSN_PROC (SSN IN OUT VARCHAR2) is
SSN_INVALID VARCHAR2 (11):=TRANSLATE(SSN,'x0123456789-','x'); --checks alphas or invalid chars
BEGIN

IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN --checks length
RAISE_APPLICATION_ERROR(-20001,'SSN Must Be Between 9 And 11 Characters');
ELSE
IF LENGTH(SSN) = 11 AND
INSTR(SSN, '-') = 4 AND --checks to see if hyphens are placed in both 4th and 7th positions
INSTR(SSN, '-',1,2) =7 AND
SUBSTR(SSN,1,3)||SUBSTR(SSN,5,2)||SUBSTR(SSN,8,4) NOT LIKE '%-%' --checks to see if hyphens are entered correctly
AND SSN_INVALID IS NULL THEN --if ssn contains any invalid chars, ssn_invalid is NOT NULL, which would raise an error
IF SUBSTR(SSN,1,1) = SUBSTR(SSN,2,1) AND SUBSTR(SSN,2,1) = SUBSTR(SSN,3,1) AND --checks to see if all chars are same
SUBSTR(SSN,3,1) = SUBSTR(SSN,5,1) AND SUBSTR(SSN,5,1) = SUBSTR(SSN,6,1) AND
SUBSTR(SSN,6,1) = SUBSTR(SSN,8,1) AND SUBSTR(SSN,8,1) = SUBSTR(SSN,9,1) AND
SUBSTR(SSN,9,1) = SUBSTR(SSN,10,1) AND SUBSTR(SSN,10,1) = SUBSTR(SSN,11,1) THEN
RAISE_APPLICATION_ERROR(-20001,'SSN May Not Contain All Same Digits');
END IF;
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly '||SSN); --does not need formatting because SSN was entered correctly
ELSE
IF LENGTH (SSN) = 10 AND --if length is 10 then hyphen can only be in 4th or 6th,
INSTR(SSN, '-') = 4 AND -- this checks if hyphen is in 4th position
SUBSTR(SSN,1,3)||SUBSTR(SSN,5,6) NOT LIKE '%-%' --checks to see if alphas are not in incorrect positions
AND SSN_INVALID IS NULL --checks alphas or invalid chars
THEN
IF SUBSTR(SSN,1,1) = SUBSTR(SSN,2,1) AND SUBSTR(SSN,2,1) = SUBSTR(SSN,3,1) AND --checks to see if all same chars
SUBSTR(SSN,3,1) = SUBSTR(SSN,5,1) AND SUBSTR(SSN,5,1) = SUBSTR(SSN,6,1) AND
SUBSTR(SSN,6,1) = SUBSTR(SSN,7,1) AND SUBSTR(SSN,7,1) = SUBSTR(SSN,8,1) AND
SUBSTR(SSN,8,1) = SUBSTR(SSN,9,1) AND SUBSTR(SSN,9,1) = SUBSTR(SSN,10,1) THEN
RAISE_APPLICATION_ERROR(-20001,'SSN May Not Contain All the Same Digits');
END IF;
SSN:= SUBSTR(SSN,1,6)||'-'||SUBSTR(SSN,7,4);

ELSE
IF LENGTH(SSN) = 10 AND --if lenth is 10 then hyphen can only be in 4th or 6th position
INSTR(SSN, '-') = 6 AND --this checks if hyphen is in 6th position
SUBSTR(SSN,1,5)||SUBSTR(SSN,7,4) NOT LIKE '%-%'
AND SSN_INVALID IS NULL
THEN
IF SUBSTR(SSN,1,1) = SUBSTR(SSN,2,1) AND SUBSTR(SSN,2,1) = SUBSTR(SSN,3,1) AND
SUBSTR(SSN,3,1) = SUBSTR(SSN,4,1) AND SUBSTR(SSN,4,1) = SUBSTR(SSN,5,1) AND
SUBSTR(SSN,5,1) = SUBSTR(SSN,7,1) AND SUBSTR(SSN,7,1) = SUBSTR(SSN,8,1) AND
SUBSTR(SSN,8,1) = SUBSTR(SSN,9,1) AND SUBSTR(SSN,9,1) = SUBSTR(SSN,10,1) THEN
RAISE_APPLICATION_ERROR(-20001,'SSN MAY NOT CONTAIN ALL SAME DIGITS');
END IF;
SSN:= SUBSTR(SSN,1,3)||'-'||SUBSTR(SSN,4,7);
ELSE
IF LENGTH (SSN) = 9 AND --if length is 9, then SSN cannot contain any hyphens
SSN NOT LIKE '%-%'
AND SSN_INVALID IS NULL
THEN
IF SUBSTR(SSN,1,1) = SUBSTR(SSN,2,1) AND SUBSTR(SSN,2,1) = SUBSTR(SSN,3,1) AND
SUBSTR(SSN,3,1) = SUBSTR(SSN,4,1) AND SUBSTR(SSN,4,1) = SUBSTR(SSN,5,1) AND
SUBSTR(SSN,5,1) = SUBSTR(SSN,6,1) AND SUBSTR(SSN,6,1) = SUBSTR(SSN,7,1) AND
SUBSTR(SSN,7,1) = SUBSTR(SSN,8,1) AND SUBSTR(SSN,8,1) = SUBSTR(SSN,9,1) THEN
RAISE_APPLICATION_ERROR(-20001,'SSN CANNOT CONTAIN ALL SAME DIGITS');
END IF;
SSN:= SUBSTR(SSN,1,3)||'-'||SUBSTR(SSN,4,2)||'-'||SUBSTR(SSN,6,4);
ELSE RAISE_APPLICATION_ERROR(-20001,'SSN Entered Incorrectly');
END IF;
END IF;
END IF;
END IF;
END IF;
END SSN_PROC;
FUNCTION SSN_FUNCTION
(SSN_VAR IN VARCHAR)
RETURN VARCHAR2
IS
TEMP_SSN VARCHAR2(20);
BEGIN
TEMP_SSN := SSN_VAR;
SSN_PROC(TEMP_SSN);
RETURN TEMP_SSN;
END SSN_FUNCTION;
END SSN_PACKAGE;
/

[andrewst]

You only have one call to PUT_LINE, which is in this IF block:

  ...
  IF LENGTH(SSN) = 11 AND
  INSTR(SSN, '-') = 4 AND --checks to see if hyphens are placed in both 4th and 7th positions
  INSTR(SSN, '-',1,2) =7 AND
  SUBSTR(SSN,1,3)||SUBSTR(SSN,5,2)||SUBSTR(SSN,8,4) NOT LIKE '%-%' --checks to see if hyphens are entered correctly
  AND SSN_INVALID IS NULL THEN --if ssn contains any invalid chars, ssn_invalid is NOT NULL, which would raise an error
    IF SUBSTR(SSN,1,1) = SUBSTR(SSN,2,1) AND SUBSTR(SSN,2,1) = SUBSTR(SSN,3,1) AND --checks to see if all chars are same
    SUBSTR(SSN,3,1) = SUBSTR(SSN,5,1) AND SUBSTR(SSN,5,1) = SUBSTR(SSN,6,1) AND
    SUBSTR(SSN,6,1) = SUBSTR(SSN,8,1) AND SUBSTR(SSN,8,1) = SUBSTR(SSN,9,1) AND
    SUBSTR(SSN,9,1) = SUBSTR(SSN,10,1) AND SUBSTR(SSN,10,1) = SUBSTR(SSN,11,1) THEN
      RAISE_APPLICATION_ERROR(-20001,'SSN May Not Contain All Same Digits');
    END IF;
    DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly '||SSN); --does not need formatting because SSN was entered correctly
  ELSE
    ...

I suggest you make it the last line of your procedure.

The code could be improved quite a lot - it's extremely repetetive and verbose at the moment. Maybe you should split out the test for all digits not being the same from the other checks, so that it only appears once - something like this:

ssn_digits := TRANSLATE(ssn,'0-','0'); -- Remove the hyphens
digit1 := SUBSTR(ssn,1,1); -- get first digit
-- test all other digits
FOR i IN 2..9 LOOP
  IF SUBSTR(ssn,i,1) = digit1 THEN
    RAISE_APPLICATION_ERROR(-20001,'SSN MAY NOT CONTAIN ALL SAME DIGITS');
  END IF;
END LOOP;

Or even slicker:

ssn_digits := TRANSLATE(ssn,'0-','0'); -- Remove the hyphens
digit1 := SUBSTR(ssn,1,1); -- get first digit
-- test all other digits
ssn_digits := TRANSLATE(ssn,'x'||digit1, 'x') -- Remove all digit1's
IF LENGTH(ssn_digits) = 0 THEN
  RAISE_APPLICATION_ERROR(-20001,'SSN MAY NOT CONTAIN ALL SAME DIGITS');
END IF;

[oraculous]

thanx tony!! but i am not quite as advanced at this as you (obviously)...i am just starting out so i dont even know exactly how to read your code yet. it took me forever just to get to this point, and it is due tomorrow.

there is just no way to add something simple to make it appear? the code does work, i know it may look like spaghetti but my professor just wants it to work.

could anything just be added at those lines to just make it appear back formatted? i tried to dbms_output.put_line(ssn) after initializing SSN to the formatted SSN, but it didnt work that way.

May i ask where you found out about the translate function? it is really a helpful function and most people in class never heard of it. you were actually a great help to others in my class as well.

[andrewst]

Originally posted by oraculous
could anything just be added at those lines to just make it appear back formatted? i tried to dbms_output.put_line(ssn) after initializing SSN to the formatted SSN, but it didnt work that way.

Like I said, just move the PUT_LINE statement right down to just before the END. If no errors has been raised by the time you get there, the PUT_LINE will work.

Originally posted by oraculous
May i ask where you found out about the translate function? it is really a helpful function and most people in class never heard of it. you were actually a great help to others in my class as well.

A good place to start is the SQL Reference manual:

http://otn.oracle.com/docs/products/...ns2a.htm#80856

All the Oracle manuals are available free and on-line here:

http://otn.oracle.com/docs/products/...v/docindex.htm