Why I get NO_DATA_FOUND in this anonymous block?

Question

CREATE OR replace FUNCTION Ord_ship_se (p_basketid IN bb_basket.idbasket%TYPE)
RETURN VARCHAR2
IS
  lv_stat    VARCHAR2(10);
  lv_numdays CHAR;
  lv_ordered bb_basket.dtordered%TYPE;
  lv_created bb_basket.dtcreated%TYPE;
BEGIN
    SELECT dtcreated,
           dtordered
    INTO   lv_created, lv_ordered
    FROM   bb_basket
    WHERE  p_basketid = idbasket;

    lv_numdays := To_char(lv_created - lv_ordered);

    IF lv_numdays = 1 THEN
      lv_stat := 'OK';
    ELSE
      lv_stat := 'CHECK';
    END IF;

    RETURN lv_stat;
END;

The above code calculates the number of days between the creation date and shipping date. I'm trying to create an anonymous block to test the outcome.

The idstage column of the BB_BASKETSTATUS table indicates a shipped item with the value 5, and the DTSTAGE column is the shipping date. The dtordered column of the BB_BASKET table is the order date.

This is what I have so far

DECLARE
  lv_numDays CHAR;
  lv_stat VARCHAR2(12);
BEGIN
  lv_stat := ORD_SHIP_SE(lv_numDays);
  DBMS_OUTPUT.PUT_LINE(lv_stat);
END;

Why I keep getting error ORA-01403: no data found at line 10 in ord_ship_se?

also the variable lv_numDays doesnt have a value stored in it so the value that will be pass on the procedure will be `NULL` — brenners1302, Oct 15 '15 at 23:47
You are passing `lv_numDays` into something that expects a basket id. — WW., Oct 15 '15 at 23:48
@brenners1302 sorry I edited the problem, I keep getting an error that no data found at line 10 in ord_ship_se — TheNewGuy, Oct 15 '15 at 23:48

score 0 · Answer 1 · answered Oct 15 '15 at 23:53

What you need is to check whether your query is returning something. Since you are using a select into your function should be like:

create or replace 
   FUNCTION ORD_SHIP_SE (p_basketid IN bb_basket.idbasket%type)
     RETURN VARCHAR2
   IS
    lv_stat VARCHAR2(10);
    lv_numDays CHAR;
    lv_ordered    bb_basket.dtordered%type;
    lv_created    bb_basket.dtcreated%type;
BEGIN
  BEGIN
    SELECT dtcreated, dtordered
      INTO lv_created, lv_ordered
      FROM bb_basket
     WHERE idbasket = p_basketid;
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
         lv_stat := 'CHECK';
         RETURN lv_stat;
  END;

     lv_numDays := TO_CHAR(lv_created-lv_ordered);
     IF lv_numDays = 1 THEN
         lv_stat:='OK';
     ELSE 
         lv_stat := 'CHECK';
     END IF;

RETURN lv_stat;

END;

You have to check if your query returned something in order to continue your function. I've added the check with the begin exception when no_data_found block so you can change it as your will.

Also you didn't pass any value on lv_numDays to your function which means that it will be null to the function. as stated in the comments.

Even though you have passed the value and the error is gone you should add the check on that query because once you pass a value that is not in your database you will have the same error.

Why I get NO_DATA_FOUND in this anonymous block?

1 Answers1