Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Seems to be perfect.. but fails

Re: Seems to be perfect.. but fails

From: Bob Cunningham <bcunn_at_oanet.com>
Date: Sat, 29 Aug 1998 18:26:43 GMT
Message-ID: <35e845b7.46146092@news.oanet.com>


On Fri, 28 Aug 1998 11:07:48 -0400, "Dwight Crane" <dwight.crane_at_mci.com> wrote:

>Ok.. this might take some explaining.. below is the code... but first I will let you know that I am passing 4 paramaters (ie.
>212,818,212,457)... in thorough testing... these parameters are being passed correctly and assigned to the proper variables...
>This code works UNTIL it actually finds a positive match.... I have even manually entered the "s" values... ran the sql script
>against the dbase and it returned the value it should have (ie. SNAOTXIRDS8)... so the FROM and WHERE clause executes fine.. where
>it fails is trying to put this value into the SWAP variable.... I even tried "SELECT '12345678911' into SWAP" so that I know it is
>trying to populate with a correct value... but it seems to not like the INTO ... I am plumb out of IDEAS..
>
>
>****************
>FUNCTION LERG_CLLI(sLECNPA IN VARCHAR2, sLECNXX IN VARCHAR2,sMCINPA IN VARCHAR2, sMCINXX IN VARCHAR2)
>RETURN VARCHAR2 IS RET_LERG_CLLI VARCHAR2(11);
> SWAP VARCHAR2 (11);
>
> BEGIN
> SELECT DISTINCT LEC_SWTCH_CLLI INTO SWAP
> FROM CIS_COLLO_T
> WHERE (LEC_NPA = sLECNPA) AND
> (LEC_NXX = sLECNXX) AND
> (MCI_NPA = sMCINPA) AND
> (MCI_NXX = sMCINXX);
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> null;
> IF SWAP is NULL THEN
> RET_LERG_CLLI :='N/A';
> ELSE RET_LERG_CLLI := SWAP;
> END IF;
> RETURN RET_LERG_CLLI;
>END;
>
>
>
>*********************
>Dwight
>
>

You've included your test of SWAP, and return of RET_LTERG_CLLI, in the EXCEPTION block, so they will only execute when the NO_DATA_FOUND exception occurs. When data is actually found, then that code won't execute and the Function will conclude without issuing a RETURN at all (hence your problem). To get the code out of the exception block you'll have to add another BEGIN/END to frame the select statement and exception block as follows:

FUNCTION LERG_CLLI...
BEGIN
    BEGIN

        SELECT  DISTINCT LEC_SWTCH_CLLI INTO SWAP
       FROM CIS_COLLO_T
       WHERE (LEC_NPA = sLECNPA) AND
       (LEC_NXX = sLECNXX) AND
       (MCI_NPA = sMCINPA) AND
       (MCI_NXX = sMCINXX);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          null;

    END;     IF SWAP is NULL THEN

        RET_LERG_CLLI :='N/A';
    ELSE RET_LERG_CLLI := SWAP;
    END IF;
    RETURN RET_LERG_CLLI;
END; I also assume that you're aware that the SELECT INTO can only return a single row. If your WHERE clause qualifies more than one row, then the SELECT INTO will raise another exception (TOO_MANY_ROWS).

HTH Bob Cunningham
bcunn_at_oanet.com Received on Sat Aug 29 1998 - 13:26:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US