Re: Trouble with Trigger raising "no data found"
Date: 26 Sep 2002 20:20:20 -0700
Message-ID: <92eeeff0.0209261920.28b8184f_at_posting.google.com>
postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0209260014.3f52887_at_posting.google.com>...
> 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;
> > /
>
If you want to use CURSOR attributes (%FOUND, %NOTFOUND) without NO_DATA_FOUND exception handling, then you can shift your SELECT INTO's into a CURSOR. e.g.
DECLARE
dummy_ NUMBER;
CURSOR cur_ IS
SELECT ... FROM ......;
BEGIN
OPEN cur_;
FETCH cur_ INTO dummy_;
- NO_DATA_FOUND exception handling not required IF cur_%NOTFOUND THEN -- This is always tested ... END IF; CLOSE cur_; END; /
Another caveat with SELECT INTO is that if you have a SQL group
function then NO_DATA_FOUND exception is never raised. That is because
group functions such as AVG and SUM always return a value or a null.
In such cases, %NOTFOUND
always evaluates to FALSE. e.g.
DECLARE
my_sal_ NUMBER(7,2);
my_dept_ NUMBER;
BEGIN
SELECT MAX(sal) INTO my_sal_ FROM emp WHERE deptno = my_dept_;
- never raises NO_DATA_FOUND IF SQL%NOTFOUND THEN -- always tested but never true ... -- this action is never taken END IF; END; /
/Rauf Sarwar Received on Fri Sep 27 2002 - 05:20:20 CEST