Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Seems to be perfect.. but fails
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;
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
![]() |
![]() |