Re: Trouble with Trigger raising "no data found"

fibbert_at_yahoo.com (Robert) wrote in message news:<4801dfd7.0209250711.247d122d_at_posting.google.com>...
> Hi Everyone,
>
> I am having a very frustrating trigger issue: I have written a
> trigger which calls a separately defined function (text of the
> function is below). This function checks to see if there is an
> "override" price for a part from a specific vendor - and if not,
> returns the default price.
>
> What is happening is that the Function raises an ORA-01403 no data
> found error after the SELECT VENDOR_PART_ID FROM VENDOR_PART ... in
> the function below. It is not getting to the IF SQL%FOUND THEN line.
>
> Now - I have no EXCEPTION section in either the funciton or the
> trigger code and I'm trying to use cursor attributes to detect the no
> data condition - but it's not working. Could this be a bug? Is this
> as designed?
>
> Thanks
>
> Function:
>
> CREATE OR REPLACE FUNCTION
> TK_CORRECT_UNIT_PRICE
> (
> sVendorID IN VENDOR.ID%TYPE,
> sPartID IN PART.ID%TYPE,
> nQty IN PURC_ORDER_LINE.ORDER_QTY%TYPE
> )
> RETURN NUMBER
> IS
> /* Variable declaration goes here */
> nPrice VENDOR_QUOTE.DEFAULT_UNIT_PRICE%TYPE := 0;
> rowVendorQuote VENDOR_QUOTE%ROWTYPE;
> sVendorPartID VENDOR_QUOTE.VENDOR_PART_ID%TYPE := '';
> BEGIN
> /* Methods and procedures go here */
>
> SELECT VENDOR_PART_ID INTO sVendorPartID
> FROM VENDOR_PART
> WHERE PART_ID = sPartID AND
> VENDOR_ID = sVendorID;
>
> > ERROR OCCURS HERE
>
> if SQL%FOUND THEN
>
>
> SELECT *
> INTO
> rowVendorQuote
> FROM
> VENDOR_QUOTE
> WHERE
> VENDOR_ID = sVendorID AND
> VENDOR_PART_ID = sVendorPartID;
>
>
>
> if SQL%NOTFOUND THEN -- We did not find a row matching our
> criteria
>
> SELECT
> (NVL(UNIT_MATERIAL_COST,0) + NVL(UNIT_SERVICE_COST,0))
> INTO
> nPrice
> FROM
> Part
> WHERE
> ID = sPartID;
>
> if SQL%NOTFOUND THEN
> raise_application_error(-20999, 'VMFG-20504 Errors occurred in
> method TK_CORRECT_UNIT_PRICE');
> nPrice := 0;
> End if;
>
> Else
> --We did find a matching row in VENDOR_QUOTE, Now we need to find
> out what price is
> --Correct for the given materials
>
>
> if (rowVendorQuote.QTY_BREAK_5 < nQty) and
> (rowVendorQuote.QTY_BREAK_5 IS NOT NULL) then
> nPrice := rowVendorQuote.UNIT_PRICE_5;
> elsif (rowVendorQuote.QTY_BREAK_4 < nQty) and
> (rowVendorQuote.QTY_BREAK_4 IS NOT NULL) then
> nPrice := rowVendorQuote.UNIT_PRICE_4;
> elsif (rowVendorQuote.QTY_BREAK_3 < nQty) and
> (rowVendorQuote.QTY_BREAK_3 IS NOT NULL) then
> nPrice := rowVendorQuote.UNIT_PRICE_3;
> elsif (rowVendorQuote.QTY_BREAK_2 < nQty) and
> (rowVendorQuote.QTY_BREAK_2 IS NOT NULL) then
> nPrice := rowVendorQuote.UNIT_PRICE_2;
> elsif (rowVendorQuote.QTY_BREAK_1 < nQty) and
> (rowVendorQuote.QTY_BREAK_1 IS NOT NULL) then
> nPrice := rowVendorQuote.UNIT_PRICE_1;
> else
> nPrice := rowVendorQuote.DEFAULT_UNIT_PRICE;
> end if;
>
> End if;
>
> Else
>
> SELECT
> (NVL(UNIT_MATERIAL_COST,0) + NVL(UNIT_SERVICE_COST,0))
> INTO
> nPrice
> FROM
> Part
> WHERE
> ID = sPartID;
>
> if SQL%NOTFOUND THEN
> raise_application_error(-20999, 'VMFG-20504 Errors occurred in
> method TK_CORRECT_UNIT_PRICE');
> nPrice := 0;
> End if;
>
> End If;
>
>
> RETURN(nPrice);
>
> END;
> /

It is working as designed.
You need to wrap the select into's in their own begin end block (inside the current code) with their own exception handler. The exception section should read
when no_data_found then raise_application_error etc.

Hth

Sybrand Bakker
Senior Oracle DBA Received on Thu Sep 26 2002 - 10:14:47 CEST