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

    Question Unanswered: Help with Dbms_output PLEASE!!!

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

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

    Re: Help with Dbms_output PLEASE!!!

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

    Code:
      ...
      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:

    Code:
    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:

    Code:
    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;

  3. #3
    Join Date
    Apr 2003
    Posts
    7
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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

Posting Permissions

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