Results 1 to 8 of 8

Thread: Call a function

  1. #1
    Join Date
    Jun 2011
    Posts
    18

    Unanswered: Call a function

    Hi All,

    I am trying to call this function but i want to pass it two parameters and I am in a bit of a bind here. the function is suppose to return a phone number based on address type in MA or SA, if I change the passed parameter to either SA or MA I get the required result, what am I doing wrong or better still - based on what I have, how to I manipulate the code to give me both as a passed param.

    Thanks in Advance,


    create or replace FUNCTION "X_F_GET_TELEPHONE" (PIDM SPRTELE.SPRTELE_PIDM%TYPE, TELE_CODE SPRTELE.SPRTELE_TELE_CODE%TYPE)
    RETURN VARCHAR2 AS

    --THIS FUNCTION RETURNS THE WHOLE PHONE STRING

    AREA_CODE SPRTELE.SPRTELE_PHONE_AREA%TYPE :=NULL;
    PHONE_NUMBER SPRTELE.SPRTELE_PHONE_NUMBER%TYPE :=NULL;
    RETVAL VARCHAR2(16) :=NULL;

    --GET THE PRIMARY ONE
    CURSOR GET_PHONE_1 IS
    SELECT SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER
    FROM SPRTELE
    WHERE SPRTELE_PIDM = PIDM
    AND SPRTELE_TELE_CODE = TELE_CODE
    AND SPRTELE_PRIMARY_IND = 'Y'
    AND SPRTELE_STATUS_IND IS NULL;

    --USE THIS IN CASE NO PRIMARY EXISTING
    CURSOR GET_PHONE_2 IS
    SELECT SPRTELE_PHONE_AREA, SPRTELE_PHONE_NUMBER
    FROM SPRTELE
    WHERE SPRTELE_PIDM = PIDM
    AND SPRTELE_TELE_CODE = TELE_CODE
    AND SPRTELE_STATUS_IND IS NULL
    ORDER BY SPRTELE_STATUS_IND,
    SPRTELE_ADDR_SEQNO DESC;

    BEGIN
    OPEN GET_PHONE_1;
    FETCH GET_PHONE_1 INTO AREA_CODE, PHONE_NUMBER;
    IF GET_PHONE_1%NOTFOUND THEN
    OPEN GET_PHONE_2;
    FETCH GET_PHONE_2 INTO AREA_CODE, PHONE_NUMBER;
    CLOSE GET_PHONE_2;
    END IF;
    CLOSE GET_PHONE_1;

    IF AREA_CODE IS NOT NULL THEN
    IF PHONE_NUMBER IS NOT NULL THEN
    RETVAL := '(' || AREA_CODE || ') ' || SUBSTR(PHONE_NUMBER, 1, 3) || '-' || SUBSTR(PHONE_NUMBER, 4, LENGTH(PHONE_NUMBER));
    END IF;
    END IF;

    RETURN RETVAL;
    EXCEPTION
    WHEN OTHERS THEN
    RETURN NULL;
    END;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >EXCEPTION
    >WHEN OTHERS THEN
    >RETURN NULL;

    FATAL FLAW!
    Eliminate completely
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm afraid I don't understand the question. OK, this is the function. But - what are "MA" or "SA"? What does this mean:
    how to I manipulate the code to give me both as a passed param.
    How exactly do you call the function? What do you expect it to return (i.e. what is "give me both"? Both what?).

    Perhaps you could provide a simple test case (CREATE TABLE and INSERT INTO several sample records, so that we could try it ourselves), as well as function call and expected result.

    Please, enclose code (in the future) into the [code] tags and format it so that it is easier to read.

    As of your function: why do you use cursors here? The same could have been done with simple SELECT statements.
    Also, remove that silly WHEN OTHERS exception handler. It is a huge mistake ... basically, you said that whatever happens, you don't care and the function returns NULL. Is it because there is really no telephone number (so NULL is a valid result), or there was a serious error but you chose to hide it. That's wrong; catch every exception you know, if you wish (such as NO-DATA-FOUND or TOO-MANY-ROWS), but let Oracle raise other errors.

  4. #4
    Join Date
    Jun 2011
    Posts
    18
    The function is returning students' address type as in MA = Mailing or SA = Summer Address, I am new to this and absorbing as I get along. As for the parameter passed, if the students' mailing address is MA select it if not select SA instead.

    I am going to delete this
    EXCEPTION
    WHEN OTHERS THEN
    RETURN NULL;

    and this
    CURSOR
    As for the parameter passed again this is what i want it to look like

    FUNCTION "X_F_GET_TELEPHONE(PK_ID NUMBER, 'MA', 'SA')
    at the moment when i pass either MA or SA the desired result is displayed but not both(MA and SA)

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >FUNCTION "X_F_GET_TELEPHONE(PK_ID NUMBER, 'MA', 'SA')
    >at the moment when i pass either MA or SA the desired result is displayed but not both(MA and SA)
    Since a FUNCTION can be definition only return a single value,
    which one should be returned when both are requested?????????
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2011
    Posts
    18
    Actually, it does not allow for 2 input, it only takes one of the parameter - so thats is why or where i am having issues

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    make two separate functions; one for each type
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jun 2011
    Posts
    18
    i did but i would like to display them both on one column for students that have either mailing address indicator

Posting Permissions

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